SQL Left Join

has average rating 7 out of 10. Total 21 users rated.

<<PreviousNext>>

Description

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;

Parameters

Pictorial representation

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;

Explanation

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.

Output

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

Pictorial Presentation of the above example :

See our Model Database

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.

You might be interested to read the following topics :

• SQL Joins
• SQL Right Join