Sql Outer Join

How to utilize sql outer join, left outer join or right outer join.


Sql inner join, or just known as sql join, shows only rows that satisfy the join condition. But sometimes, you may want to display all rows from one table regardless. To do that, we use outer join.

A Sql outer join shows the join, and all the rows that do not qualify. If the first table is to be included regardless, it is called left outer join. While if the second table is to be included, it is called right outer join. Generally we can replace right outer join with left outer join by repositioning the tables. See the following diagram for visual explanation.

left-outer-join right-outer-join

The Syntax

Sql left outer join

SELECT column_list
FROM   table_a a
       LEFT JOIN table_a b
         ON a.id = b.id 

AND

Sql right outer join

SELECT column_list
FROM   table_a a
       RIGHT JOIN table_a b
         ON a.id = b.id 

Example

At one time, I thought that outer join is a bit weird. Why would you need to join two tables and also show the unmatched join?

But once I know how to use it, I have to admit that it is very powerful.

Let's use PUBS database again. We can use the sql left join to find out whether there is any Publishers who have not published any book.

Sql Command

SELECT  pb.*,  ttls.title
FROM  publishers  pb  LEFT  JOIN  titles  ttls
ON  pb.pub_id = ttls.pub_id

Results

pub_idpub_namecitystatecountrytitle
736New Moon BooksBostonMAUSAYou Can Combat Computer Stress!
736New Moon BooksBostonMAUSAIs Anger the Enemy?
.  .  .  .  .  .  .  .  .  .  .  {I hide this boring rows}.  .  .  .  .  .  .  .  .  .  . 
1622Five Lakes PublishingChicagoILUSANULL
1756Ramona PublishersDallasTXUSANULL
9901GGG&GMunchenNULLGermanyNULL
9952Scootney BooksNew YorkNYUSANULL
9999Lucerne PublishingParisNULLFranceNULL

You can identify the Publishers that have not published anything from the NULL value of Title column. Those rows are the 'outer part' of the join.

This solution is , well, OK for this problem, but you can refine it.

Remember this: the NULL value is the signature of the sql outer join. The table that doesn't have a match row will show Null value.

Rewrite the Sql command again, now add a WHERE clause:

Sql Command

SELECT pb.*,
       ttls.title
FROM   publishers pb
       LEFT JOIN titles ttls
         ON pb.pub_id = ttls.pub_id
WHERE  ttls.title IS NULL 

Results

pub_idpub_namecitystatecountrytitle
1622Five Lakes PublishingChicagoILUSANULL
1756Ramona PublishersDallasTXUSANULL
9901GGG&GMunchenNULLGermanyNULL
9952Scootney BooksNew YorkNYUSANULL
9999Lucerne PublishingParisNULLFranceNULL

It's now showing just the Publishers that didn't publish anything.

Later, you'll use the sql outer join again to solve the Duplicates problem. We'll discuss this in the Problems and Solutions page.

Quick link. From here, you can learn the following:

Data Creation

Sql Create Database
Use this to create a database.

Sql Create Table
This Sql Create Table will be used very often throughout the tutorial. It is used to create a table, in our case, mainly to create sample tables.

Sql Alter Table
Used less often, but it is very handy to know this command. With this you can basically edit a table structure.

Sql Drop Table
Used to delete a table, not just the content. This command is also used very often during the tutorial. So, you need to master this too.

Data Manipulation

Sql Insert
To add rows to a table, you use Sql Insert command. There are two methods, one is with the VALUES keyword and second is with a SELECT statement.

Sql Update
To change existing rows, you use Sql Update command. You can use it to change values in a single row, groups of rows or all rows. You can also update a table based on values in other tables.

Sql Delete
To remove rows, you use Sql Delete command. You can delete one rows, group of rows or all rows. You can also delete rows based on values in other tables.

Data Retrieval

Sql Select
The core of Sql, selecting data from table.

Sql Distinct
Eliminating duplicate values in the output.

Sql Where
Conditionally selecting rows from table.

Sql Like
Pattern searching for conditions in Where or Having clause

Sql Between
Added to conditions to show results between two values.

Sql In
It enables us to specify multiple values, either as a list or a subquery.

Sql And Or
Connecting two or more conditions in the Where or Having clause.

Sql Count
Counting number of rows.

Sql Sum
Getting the sum of a field

Sql Max
This function will return the highest value.

Sql Min
This function will return the lowest value.

Sql Avg
This function will return the average value.

Sql Order By
Sorting the output

Sql Group By
Grouping rows into sets.

Sql Having
To filter the result of aggregation

Advanced Sql Tutorial

Sql Inner Join
Use Sql Join to retrieve data from more than one table in just one Select statement.

Sql Self Join
A join that compares values within a single table.

Sql Subquery
A nested Sql Select inside the WHERE clause of another Sql Select statement.

Sql Union
Merging two or more queries into one result. Rows are appended from one queries to the other.

Journey
Buy at AllPosters.com
A bend in the road is not the end of the road... unless you fail to make the turn

Positive Resources

All of us have at least two or three things that excite us. We could talk about these subjects for hours. Good news!. Did you know that Site Built It! can turn this type of deep interest and enthusiasm into a revenue-generating business?

Review some of the Site Build It! case studies and you'll absolutely discover something that ignites more positive ideas into your mind.

SBI! Video Tour


Invest the most
important 30 minutes
you will spend online.

Click, sit back, and
understand why SBI!
makes you succeed.