Full Join by Example in SQL Server

The Full Join (Also known as Full Outer join) includes the rows from both the tables. Wherever the rows do not match a null value is assigned to every column of the table that does not have a matching row. A Full join looks like a Right Join & Left Join combined together in one query. This article explores the Full join in more detail with examples

Syntax

The syntax of the Full join is as follows

The join_condition defines the condition on which the tables are joined.

All the rows from both the tables are included in the final result. A NULL value is assigned to every column of the table that does not have a matching row based on the join condition.

The FULL OUTER JOIN & FULL JOIN are the same. The Keyword OUTER is optional

Full Join

The Full join can be better explained with the following Venn diagram

Full Join Venn Diagram
Full Join Venn Diagram

The circles in the above diagram represent the two tables. Table A and Table B, which we would like to join using the full join. The intersection part in the above picture shows the data rows which satisfy the join condition.

The full join of the above tables returns

  • The matching rows from both tables
  • All the unmatched rows from Table A with null for columns from Table B
  • All the unmatched rows from Table B with null for columns from Table A

Full Join Example

Now, let us see the full join using a real-life example

Sample database

Consider the following tables from the table reservation system of a restaurant. You can use the following script to create the database

CustomerType : Type of Customer like VIP / Regular etc
Customers List of customers with Customer Type.
Tables The list of tables available in the restaurant. CustomerID field indicates that the customer has reserved the table.
Orders : The orders placed by the customer
DiscVoucher The discounts slab based on the total value of the order

--Sample database--

Sample database structure for Join Query example
Sample database structure for Join Query example

--SQL Query--

Query

The following query joins the customers with the tables

The above query returns all the customers & all the tables. The result shows

  1. Customers who have booked the tables (matching rows)
  2. Customers who have not reserved the table. The Table No column has NULL Value
  3. Unreserved tables. The customerID & Name column has NULL Values
Result of Full Join example query

Where clause

By filtering out the results where CustomerID is null, it will return all the customers with reserved tables. The result is similar to what you get when you use the Left Join

Result of query with where clause

Similarly, by filtering out the result where Table No is null, the query will return the list of tables with the customer who booked it. The result is similar to Right Join

Result of query with where clause

Finally, filtering out both null customers and tableNo, you will get the result which is what you get when you use inner join

Result of query with where clause

References

  1. Join Syntax (SQL Server)
  2. Sample database

Summary

Full Join is a very rarely used compared to the other join types. It joins both the tables on a join condition but includes unmatched rows from both the tables into a single result set.

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top