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.
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.
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)
Which is better.. left outer join or right outer join?
As others have pointed out already LEFT OUTER JOIN and RIGHT OUTER JOIN are exactly the same operation, except with their arguments reversed. Your question is like asking whether it is better to write a < b or b > a. They're the same - it's just a matter of preference.
Having said that, I find that most people are more familiar with LEFT JOIN and use it consistently in their SQL. Some people even find it quite difficult to read if there is suddenly a RIGHT JOIN in the middle of a query, and it causes them to have to stop and think what it means. So I'd suggest that given an equal choice between the two options, prefer to use LEFT JOIN. Being consistent will make it easier for other developers to understand your 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.
Why does OUTER JOIN need an equality rule?
You are confusing a cross join with a inner/outer(left,right,full) joins. A cross join will match each row with every other row without a condition. The inner/outer joins match on those satisfying the condition in the ON clause. The primary difference among the inner and outer joins is how a non-match is handled. The non-match concept does not apply to a cross join.
Try running these queries as an example. Note the last couple of queries using a full outer join. The ON clause with 1=1 simulates the cross join. The ON clause with 1=0 returns one record from each table.
BTW, I added a ORDER BY in the ON clause with 1=1 because the order was not that of the cross join. (Wanted it easy to compare.) Even without the ORDER BY clause, the cost of this trick is about 100x using a cross join for this very small example.
set nocount on
print 'Inner join:'
select *
from (
values (1,'A'),(2,'B'),(3,'C'),(25,'Y')
) t1 (id, val)
inner join (
values (1, 'A'),(2,'B'),(3,'C'),(26,'Z')
) t2 (id, val)
ON t1.id = t2.id
print 'Left outer join:'
select *
from (
values (1,'A'),(2,'B'),(3,'C'),(25,'Y')
) t1 (id, val)
left outer join (
values (1, 'A'),(2,'B'),(3,'C'),(26,'Z')
) t2 (id, val)
ON t1.id = t2.id
print 'Right outer join:'
select *
from (
values (1,'A'),(2,'B'),(3,'C'),(25,'Y')
) t1 (id, val)
right outer join (
values (1, 'A'),(2,'B'),(3,'C'),(26,'Z')
) t2 (id, val)
ON t1.id = t2.id
print 'Full outer join:'
select *
from (
values (1,'A'),(2,'B'),(3,'C'),(25,'Y')
) t1 (id, val)
full outer join (
values (1, 'A'),(2,'B'),(3,'C'),(26,'Z')
) t2 (id, val)
ON t1.id = t2.id
print 'Cross join:'
select *
from (
values (1,'A'),(2,'B'),(3,'C'),(25,'Y')
) t1 (id, val)
cross join (
values (1, 'A'),(2,'B'),(3,'C'),(26,'Z')
) t2 (id, val)
print 'Full outer join with everything matching anything - a fake cross join:'
select *
from (
values (1,'A'),(2,'B'),(3,'C'),(25,'Y')
) t1 (id, val)
full outer join (
values (1, 'A'),(2,'B'),(3,'C'),(26,'Z')
) t2 (id, val)
ON 1 = 1
ORDER BY t1.id, t2.id
print 'Full outer join with no matches ever:'
select *
from (
values (1,'A'),(2,'B'),(3,'C'),(25,'Y')
) t1 (id, val)
full outer join (
values (1, 'A'),(2,'B'),(3,'C'),(26,'Z')
) t2 (id, val)
ON 1 = 0
Inner join:
id val id val
----------- ---- ----------- ----
1 A 1 A
2 B 2 B
3 C 3 C
Left outer join:
id val id val
----------- ---- ----------- ----
1 A 1 A
2 B 2 B
3 C 3 C
25 Y NULL NULL
Right outer join:
id val id val
----------- ---- ----------- ----
1 A 1 A
2 B 2 B
3 C 3 C
NULL NULL 26 Z
Full outer join:
id val id val
----------- ---- ----------- ----
1 A 1 A
2 B 2 B
3 C 3 C
NULL NULL 26 Z
25 Y NULL NULL
Cross join:
id val id val
----------- ---- ----------- ----
1 A 1 A
1 A 2 B
1 A 3 C
1 A 26 Z
2 B 1 A
2 B 2 B
2 B 3 C
2 B 26 Z
3 C 1 A
3 C 2 B
3 C 3 C
3 C 26 Z
25 Y 1 A
25 Y 2 B
25 Y 3 C
25 Y 26 Z
Full outer join with everything matching anything - a fake cross join:
id val id val
----------- ---- ----------- ----
1 A 1 A
1 A 2 B
1 A 3 C
1 A 26 Z
2 B 1 A
2 B 2 B
2 B 3 C
2 B 26 Z
3 C 1 A
3 C 2 B
3 C 3 C
3 C 26 Z
25 Y 1 A
25 Y 2 B
25 Y 3 C
25 Y 26 Z
Full outer join with no matches ever:
id val id val
----------- ---- ----------- ----
1 A NULL NULL
2 B NULL NULL
3 C NULL NULL
25 Y NULL NULL
NULL NULL 1 A
NULL NULL 2 B
NULL NULL 3 C
NULL NULL 26 Z
Related Topics
Pls-00428: an into Clause Is Expected in This Select Statement
Can a SQL Server Table Have Two Identity Columns
Does Postgres Support Nested or Autonomous Transactions
Convert Text Value in SQL Server from Utf8 to Iso 8859-1
Selecting Random Rows in MySQL
Comma Separated Values in a Database Field
Support Union Function in Bigquery SQL
Dynamic Update Statement with Variable Column Names
How to Remove Redundant Namespace in Nested Query When Using for Xml Path
Select Top X (Or Bottom) Percent for Numeric Values in MySQL
What's the Best to Check If Item Exist or Not: Select Count(Id)Or Exist(...)
Casting Null Type When Updating Multiple Rows
SQL Server Union - What Is the Default Order by Behaviour
How to Have an Indexed View in MySQL