can any one explain in when to use left join and right join i am quit confused with this

Member Avatar

LEFT JOIN = include all records from the left 'table' and only add related data from the right 'table'

RIGHT JOIN - vice versa

Example...

SELECT table1.field5, table2.field7 FROM table1 LEFT JOIN table2 ON table1.id = table2.id2
field5 | field 7

1 | 3
2 | NULL
3 | 2
4 | 8
5 | NULL

The above shows all records from table1, but the only data for field7 shown is where a related record exists in table2. If no related record exists in table2, NULL is returned.

Here are 2 links from w3schools:

http://www.w3schools.com/sql/sql_join_left.asp

http://www.w3schools.com/sql/sql_join_right.asp

I don't usually provide links from this site, but I particularly liked the Venn diagram, and sample data is provided.

I should point out that "shows all records" means within the context of the entire SQL, e.g. taking WHERE and LIMIT and possibly subqueries and other joins into account.