Is RIGHT JOIN Ever Required?
You can always use only left Joins...
SELECT * FROM t1
LEFT JOIN t2 ON t1.k2 = t2.k2
RIGHT JOIN t3 ON t3.k3 = t2.k3
is equivilent to:
Select * From t3
Left Join (t1 Left Join t2
On t2.k2 = t1.k2)
On T2.k3 = T3.K3
In general I always try to use only Left Joins, as the table on the left in a Left Join is the one whose rows are ALL included in the output, and I like to think of it, (The Left side) as the "base" set I am performing the cartesion product (join) against ... So I like to have it first in the SQL...
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)
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.
Can a left join be expressed as a right join and vice versa?
Yes, it is true, however, I strongly recommend not to use RIGHT JOIN.
Since our natural reading order is from left to right and top to bottom, RIGHT JOIN comes very unnatural in this manner.
Also, If you'll have a mix of LEFT JOIN and RIGHT JOIN it would be very, very hard to follow through.
Example where a right join cannot be converted to left join?
Although you can always convert between right joins and left joins, the parsing of the SQL language can make this tricky in some cases. For just two tables, the two forms are interchangeable. However, for more tables, SQL parsing rules affect the outcome. SQL parses the from clause from left to right, so it implicitly puts in parentheses.
For instance, it is tempting to say:
from a right join b right join c
(This is shorthand for from a right join b on . . . right join c on . . . . I'm leaving out the on clauses, assuming they are in the customary places immediately following the joins.)
is the same as
from c left join b left join a
But, this is not strictly true. One obvious difference is that the first could represent a join condition between c and a. The second has trouble if that is the only condition.
The first is parsed as:
from (a right join b) right join c
This means that the first keeps all rows in b that don't match to a . . . and then all rows in c even if there are no matches in b.
The second:
from (c left join b) left join a
This means keep all rows in c, regardless of matches in b and a.
One difference is a row in a that matches c but not b. In the left join version, the values of the a columns will come from a. In the right join version, the values will be NULL. Of course, this depends on the on clauses; the point is that there are subtle differences.
I don't like to ponder such things. So, I basically never use right join. Left join follows the parsing rules of SQL and it has a simple interpretation: keep all rows in the first table regardless of matches in any of the others.
right join equal to left join?
Yes this is right, you can find more information if you searched:
Difference between left join and right join in SQL Server
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
A Way to Extract from a Datetime Value Data Without Seconds
When Would I Use Xml Instead of SQL
What Is a Simple and Efficient Way to Find Rows with Time-Interval Overlaps in SQL
How to Export Image Field to File
Pass String Variable in R Script to Use It in SQL Statement
Access - Compare Two Tables and Update or Insert Data in First Table
Use '=' or Like to Compare Strings in SQL
Select Rows Where Column Value Has Changed
Count the Null Columns in a Row in SQL
How to Write a Constraint Concerning a Max Number of Rows in Postgresql
Does the Jdbc Spec Prevent '' from Being Used as an Operator (Outside of Quotes)
Drop All Tables Whose Names Begin with a Certain String
Cannot Create an Instance of Ole Db Provider Microsoft.Jet.Oledb.4.0 for Linked Server Null
Entity Framework - Attribute in Clause Usage
Getting a Rank from Activerecord
How to Fill Date Gaps in MySQL