Left and right join question

[omgjtt]

I am starting to understand the join functions. I am still having a bit of a hard time with the literal meaning of right and left join.

For example when should I use a right and left join.

If these were the two tables

tbl_teacher
id | dept | teacherName |
-------------------------
01 | 02 | Mary Jones |
01 | 01 | Jane Smit |
01 | | James Joes |
01 | 02 | Joey Joes |
01 | 01 | Mike Jons |

Dept
id | dept |
-----------
01 | Computer Sci |
02 | Design |
03 | Math |

How do I determine which join to use if I want to return a query with only teachers with depts that are not null.

Also how would I return a query with the names of the depts (departments) and leave a null row for the teacher name.

I am just not understand the meaning of right, left, and inner join. Can someone please give a very basic of example of each. How is one right or left. I do not understand.

Thanks everyone. This site is helping speed up my learning and thank you in advance for any help!

- James

 

[spikepl]

http://en.wikipedia.org/wiki/Join_(SQL)

 

[omgjtt]

Thanks spikepl. I am reading that and it seems to be clearing up some misconceptions I had. Thanks again.

 

[plog]

There are Inner Joins and Outer Joins. There are three types of Outer Joins--Left and Right which are essentially the same thing and Full.

For your query (teachers without null departments) you want an inner join. If you wanted all teachers, even if the department was null you would want an Outer Join (Left or Right). If you had departments that didn't have teachers and teachers that didn't have departments and you wanted to return all teachers and departments including all null values you would want a Full Outer Join.


In summary:

Inner Join = I want only records that match between the two tables

Left/Right Join = I want all records from one table and matching data in 2nd table

Full Outer = I want all records of all tables, even if there are some nulls between them

 

[omgjtt]

Thanks @ plog,

I read the wiki page and your post and had my "aha" moment. Thanks for your help. I look forward be being more of a contributor on this board when my skill allows : )

Thanks again

James