Which type of join is used to returns all from a table even when there are no matching rows?

In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause.

Pictorial Presentation: SQL Full Outer Join

Syntax:

Syntax diagram - FULL OUTER JOIN

Example: SQL Full Outer Join

Let’s combine the same two tables using a full join.

SQL Code:

Output:

Because this is a full join, all rows (both matching and nonmatching) from both tables are included in the output. There is only one match between table table_A and table table_B, so only one row of output displays values in all columns. All remaining rows of output contain only values from table table_A or table table_B, with the remaining columns set to missing values

only one row of output displays values in all columns explain below -

Example: SQL Full Outer Join between two tables

Here is an example of full outer join in SQL between two tables.

Sample table: foods

Sample table: company

As we know the FULL OUTER JOIN is the combination of the results of both LEFT OUTER JOIN and RIGHT OUTER JOIN, so, here we are going to describe how FULL OUTER JOIN perform internally.

Pictorial Presentation SQL Full Outer Join:

Here is the SQL statement which returns all rows from the 'foods' table and 'company' table using "FULL OUTER JOIN" clause.

SQL Code:

Output:

FULL OUTER JOIN using WHERE clause

We can include a WHERE clause with a FULL OUTER JOIN to get return only those rows where no matching data between the joining tables are exist.

The following query returns only those company that have no matching food product in foods, as well as that food product in foods that are not matched to the listed company.

Output:

Full Outer Join using Union clause

A UNION clause can be used as an alternate to get the same result as FULL OUTER JOIN

Here is the example:

Here is the SQL statement:

FULL OUTER JOIN using LEFT and RIGHT OUTER JOIN and UNION clause

The following code is, the combination of LEFT OUTER JOIN and RIGHT OUTER JOIN and combined by, using UNION clause

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

Click on the following to get the slides presentation of all JOINS -

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.

Practice SQL Exercises

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: SQL RIGHT JOIN
Next: Join a table to itself

Summary: in this tutorial, you will learn about the SQL Server LEFT JOIN clause and how to query data from multiple tables.

Introduction to SQL Server LEFT JOIN clause

The LEFT JOIN is a clause of the SELECT statement. The LEFT JOIN clause allows you to query data from multiple tables.

The LEFT JOIN returns all rows from the left table and the matching rows from the right table. If no matching rows are found in the right table, NULL are used.

The following illustrates how to join two tables T1 and T2 using the LEFT JOIN clause:

SELECT select_list FROM T1 LEFT JOIN T2 ON join_predicate;

In this syntax, T1 and T2 are the left and right tables, respectively.

For each row from the T1 table, the query compares it with all the rows from the T2 table. If a pair of rows causes the join predicate to evaluate to TRUE, the column values from these rows will be combined to form a new row which is then included in the result set.

If a row from the left table (T1) does not have any matching row from the T2 table, the query combines column values of the row from the left table with NULL for each column values from the right table.

In short, the LEFT JOIN clause returns all rows from the left table (T1) and matching rows or NULL values from the right table (T2).

The following illustrates the LEFT JOIN of two tables T1(1, 2, 3) and T2(A, B, C). The LEFT JOIN will match rows from the T1 table with the rows from the T2 table using patterns:

In this illustration, no row from the T2 table matches row 1 from the T1 table; therefore, NULL is used. Rows 2 and 3 from the T1 table match rows A and B from the T2 table, respectively.

SQL Server LEFT JOIN example

See the following products and order_items tables:

Each sales order item includes one product. The link between the order_items and the products tables is the product_id column.

The following statement uses the LEFT JOIN clause to query data from the products and order_items tables:

SELECT product_name, order_id FROM production.products p LEFT JOIN sales.order_items o ON o.product_id = p.product_id ORDER BY order_id;

As you see clearly from the result set, a list of NULL in the order_id column indicates that the corresponding products have not been sold to any customer yet.

It is possible to use the WHERE clause to limit the result set. The following query returns the products that do not appear in any sales order:

SELECT product_name, order_id FROM production.products p LEFT JOIN sales.order_items o ON o.product_id = p.product_id WHERE order_id IS NULL ORDER BY order_id;

As always, SQL Server processes the WHERE clause after the LEFT JOIN clause.

The following example shows how to join three tables: production.products, sales.orders, and sales.order_items using the LEFT JOIN clauses:

SELECT p.product_name, o.order_id, i.item_id, o.order_date FROM production.products p LEFT JOIN sales.order_items i ON i.product_id = p.product_id LEFT JOIN sales.orders o ON o.order_id = i.order_id ORDER BY order_id;

Here is the output:

SQL Server LEFT JOIN: conditions in ON vs. WHERE clause

The following query finds the products that belong to the order id 100:

SELECT product_name, order_id FROM production.products p LEFT JOIN sales.order_items o ON o.product_id = p.product_id WHERE order_id = 100 ORDER BY order_id;

Let’s move the condition order_id = 100 to the ON clause:

SELECT p.product_id, product_name, order_id FROM production.products p LEFT JOIN sales.order_items o ON o.product_id = p.product_id AND o.order_id = 100 ORDER BY order_id DESC;

The query returned all products, but only the order with id 100 has the associated product’s information.

Note that for the INNER JOIN clause, the condition in the ON clause is functionally equivalent if it is placed in the WHERE clause.

In this tutorial, you have learned how to use the SQL Server LEFT JOIN clause to retrieve data from multiple related tables.