What Is The Purpose (Or Use Case) for an Outer Join in Sql

What is the purpose (or use case) for an outer join in SQL?

An example use case would be to produce a report that shows ALL customers and their purchases. That is, show even customers who have not purchased anything. If you do an ordinary join of customers and purchases, the report would show only those customers with at least one purchase.

When or why would you use a right outer join instead of left?

The only reason I can think of to use RIGHT OUTER JOIN is to try to make your SQL more self-documenting.

You might possibly want to use left joins for queries that have null rows in the dependent (many) side of one-to-many relationships and right joins on those queries that generate null rows in the independent side.

This can also occur in generated code or if a shop's coding requirements specify the order of declaration of tables in the FROM clause.

What are some good examples where SQL's OUTER JOIN is used?

In the Northwind database on the Customers and Orders table.

Doing an inner join will only give you customers that have placed orders.

Doing an outer join will get all customers and orders for customers that have placed orders.

When is a good situation to use a full outer join?

It's rare, but I have a few cases where it's used. Typically in exception reports or ETL or other very peculiar situations where both sides have data you are trying to combine.

The alternative is to use an INNER JOIN, a LEFT JOIN (with right side IS NULL) and a RIGHT JOIN (with left side IS NULL) and do a UNION - sometimes this approach is better because you can customize each individual join more obviously (and add a derived column to indicate which side is found or whether it's found in both and which one is going to win).

When to use right join or full outer join

Why would you join two tables and keep the rows that do not match of BOTH tables?

The full join has cases where it is useful.One of them is comparing two tables for differences like XOR between tables:

 SELECT * 
 FROM t1
 FULL JOIN t2
   ON t1.id = t2.id
 WHERE t1.id IS NULL 
    OR t2.id IS NULL;

Example:

t1.id ... t2.id
1         NULL
NULL      2

you could also achieve this by using two left joins.

Yes you could:

SELECT t1.*, t2.*
FROM t1
LEFT JOIN t2
  ON t1.id = t2.id
WHERE t2.id IS NULL
UNION ALL
SELECT t1.*, t2.*
FROM t2
LEFT JOIN t1
  ON t1.id = t2.id
WHERE t1.id IS NULL;

Some SQL dialects does not support FULL OUTER JOIN and we emulate it that way.
Related: How to do a FULL OUTER JOIN in MySQL?

On the other hand RIGHT JOIN is useful when you have to join more than 2 tables:

SELECT *
FROM t1
JOIN t2 
  ...
RIGHT JOIN  t3
 ...

Of course you could argue that you could rewrite it to correspodning form either by changing join order or using subqueries(inline views). From developer perspective it is always good to have tools(even if you don't have to use them)

Does Right Outer Join have any useful purpose?

It depends on what side of the join you put each table.

If you want to return all rows from the left table, even if there are no matches in the right table... you use left join.

If you want to return all rows from the right table, even if there are no matches in the left table, you use right join.

Interestingly enough, I rarely used right joins.

What is the difference between INNER JOIN and OUTER JOIN?

Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

Can I use CASE statement in a JOIN condition?

A CASE expression returns a value from the THEN portion of the clause. You could use it thusly:

SELECT  * 
FROM    sys.indexes i 
    JOIN sys.partitions p 
        ON i.index_id = p.index_id  
    JOIN sys.allocation_units a 
        ON CASE 
           WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1
           WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1
           ELSE 0
           END = 1

Note that you need to do something with the returned value, e.g. compare it to 1. Your statement attempted to return the value of an assignment or test for equality, neither of which make sense in the context of a CASE/THEN clause. (If BOOLEAN was a datatype then the test for equality would make sense.)

How to LEFT JOIN with multiple cases

You want to select data from tab1 where not exists a match in tab2.

select *
from tab1
where not exists
(
  select null
  from tab2
  where tab1.vala = tab2.val1
  and 
  ( 
    (tab1.valb = tab2.val2) or
    (tab1.vald = tab2.val4 and tab1.valb <> tab2.val2) or
    (tab1.valc = tab2.val3 and tab1.vald <> tab2.val4)
  )
);

Or, if you need the empty tab2 columns:

select *
from tab1
left outer join tab2
  on tab1.vala = tab2.val1
  and 
  ( 
    (tab1.valb = tab2.val2) or
    (tab1.vald = tab2.val4 and tab1.valb <> tab2.val2) or
    (tab1.valc = tab2.val3 and tab1.vald <> tab2.val4)
  )
)
where tab2.val1 is null; -- dismiss the matches

How to create SQL query with two inner join's and an if case

You want to select data from the first table and only show data from the other tables where appropriate. So, outer join the other tables.

select t1.id, t1.typ, t3.result
from t1
left outer join t2 on t2.typ1 = t1.typ and t1.case = 'yyy'
left outer join t3 on t3.id2 = t2.id1
order by t1.id;

What Is The Purpose (Or Use Case) for an Outer Join in Sql