can any one explain in when to use left join and right join i am quit confused with this
LEFT JOIN = include all records from the left 'table' and only add related data from the right 'table'
RIGHT JOIN - vice versa
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:
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.