Which Table Exactly Is the "Left" Table and "Right" Table in a Join Statement (Sql)

Which table exactly is the left table and right table in a JOIN statement (SQL)?

The Left table is the first table in the select. Yes, your two examples are equivalent.

Right join vs left join, which table is left vs right?

No. "left" and "right" refer to the ordering of the tables in the FROM clause. So these are equivalent:

select * 
from a left join
     b
     on a.id = b.id

select * 
from b right join
     a
     on a.id = b.id

These two on clauses do exactly the same thing:

on a.id = b.id
on b.id = a.id

They do not affect the results at all.

What is the left and right table in an intermediate SQL Join?

LEFT JOIN Table d on c.Something = d.Something

c is left and d is right.

LEFT JOIN Table e on b.Something = e.Something

b is left and e is right.

The table you're joining to the query (in this case d and e) is the right so whatever key from the previous query you're joining with is from the left table(c and b).

Are from Table1 left join Table2 and from Table2 right join Table1 interchangeable?

Select * from Table1 left join Table2 ...

and

Select * from Table2 right join Table1 ...

are indeed completely interchangeable. Try however Table2 left join Table1 (or its identical pair, Table1 right join Table2) to see a difference. This query should give you more rows, since Table2 contains a row with an id which is not present in Table1.

LINQ query join to get entries for left table where right table has no matching records

A filtered Include does exactly what you want:

var cars = context.Cars
    .Include(c => c.Parts.Where(p => p.MemberId == 1));

This doesn't generate the shorter join statement with a composite condition, but an outer join to a filtered subquery on Parts, to the same effect.

Determine which Table goes on Right and Left for JOINS

If a JOIN find matches for all rows then yes, it will look like an INNER JOIN. The OUTER part of joins is about what happens when a match cannot be found.

And the LEFT or RIGHT is saying which table's rows we always want to retain. So in a LEFT join, you'll always get all rows from the table to the left of the join, but for rows with no match on the right, we get NULLs. And for a RIGHT join, we always get all rows from the table to the right.

And as I say, if you're doing a LEFT join and every row in the left table has at least one matching row in the right table, the result will look the same as an INNER JOIN.

---

Related Topics