The SQL LEFT JOIN, joins two tables and fetches rows based on a condition, which are matching in both the tables, and the unmatched rows will also be available from the table before the JOIN clause.
Left Join : Syntax
Select * FROM table1 LEFT OUTER JOIN table2 ON table1.column_name=table2.column_name;
|table1, table2||Name of the tables participating in joining.|
|column_name||Column of the participating tables.|
SQL LEFT join fetches a complete set of records from table1, with the matching records (depending upon the availability) in table2. The result is NULL in the right side when no matching will take place.
Example of SQL left join or left outer join
Sample table : foods
Sample table : company
To get 'company_name' and 'company_id' columns from 'company' table and 'company_id', 'item_name', 'item_unit' columns from 'foods' table, after an OUTER JOINING with these mentioned tables, the following SQL statement can be used :
SELECT company.company_id,company.company_name, company.company_city,foods.company_id,foods.item_name FROM company LEFT OUTER JOIN foods ON company.company_id = foods.company_id;
This SQL statement would return all rows from the 'company' table and only those rows from the 'foods' table where the joined fields are equal and if the ON clause matches no records in the 'foods' table, the join will still return rows, but the NULL in each column of right table.
Key points to remember
Click on the following to get the slides presentation -
Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.
Pictorial Presentation of the above example :
Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.