Joins
Syntax:
SELECT field1, field2, field3 FROM firsttable
INNER JOIN secondtable
ON firsttable.keyfield = secondtable.foreign_keyfield
Example:
SELECT Purchase.Name, Orders.Product FROM Purchase
INNER JOIN Orders
ON Purchase.PurchaseID=Orders.PurchaseID
Left Join
syntax:
SELECT field1, field2, field3 FROM firsttable
LEFT JOIN secondtable
ON firsttable.keyfield = secondtable.foreign_keyfield
Example
SELECT Purchase.Name, Orders.Product
FROM Purchase LEFT JOIN Orders
ON Purchase.PurchaseID=Orders.PurchaseID
Left Join- returns all the rows from the first table (Purchase), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.
RIGHT JOIN
syntax
SELECT field1, field2, field3
FROM firsttable
RIGHT JOIN secondtable
ON firsttable.keyfield = secondtable.foreign_keyfield
Example
SELECT Purchase.Name, Orders.Product
FROM Purchase
RIGHT JOIN Orders
ON Purchase.PurchaseID=Orders.Purchase
