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

Question

What makes a given table the left table?

Is it that the table is indicated in the "From" part of the query?

Or, is it the left table because it is on the left hand side of the = operator?

Are the following equivalent

SELECT *
FROM left_table
LEFT JOIN right_table ON left_table.right_id = right_table.id

and

SELECT *
FROM left_table
LEFT JOIN right_table on right_table.left_id = left_table.id

???

Thanks

Counterfeit

Your second query seems to be confusing, why not writing : SELECT * FROM left_table LEFT JOIN right_table on right_table.id = left_table.right_id – Warmup

Answer 1

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

Auscultation

This needs clarification. What do you mean by "the first table in the select"? – Noshow

I think it should say "the left table is the first table in the FROM clause" – Geology

Answer 2

The right table is always the table that you are joining on. So yes, both of your statements are equivalent.

JOIN [Table] ON ...

[Table] is always the right table.

Circuitous

This begs the question of what "the table that you are joining on" means. – Benevolence

Answer 3

Roughly "left" is the result of everything that appears first in the whole FROM clause when reading from left to right - including the result of other JOINs, sub-queries, VIEWs and STORED PROCEDURES.

Both SQL statements are equivalent because the = operator at the ON part of the JOIN clause is symmetric (if a = b then b = a) so the result is the same no matter the order.

The regular join shows only the lines where the ON clause of the JOIN is true, while the LEFT JOIN shows also the records from "left" if the condition is false (showing NULL for any column from "right" present in the SELECT).

For example:

-- People:           -- Car
id | name            owner_id | model
---+------------     ---------+------------
1  | Paul            1        | Ferrari
2  | Nancy           2        | Porsche
3  | Arthur          NULL     | Lamborghini
4  | Alfred          10       | Maserati

> select people.name, car.model from people join car on car.owner_id=people.id;

name     | model
---------+--------------
Paul     | Ferrari
Nancy    | Porsche
2 record(s) found

> select people.name, car.model from people left join car on 
  car.owner_id=people.id;

name     | model
---------+--------------
Paul     | Ferrari
Nancy    | Porsche
Arthur   | NULL
Alfred   | NULL     
4 record(s) found

> select people.name, car.model from people left join car on 
  people.id = car.owner_id;

name     | model
---------+--------------
Paul     | Ferrari
Nancy    | Porsche
Arthur   | NULL
Alfred   | NULL     
4 record(s) found

Synoptic

Answer 4

See this for a pretty good walkthrough on joins: http://en.wikipedia.org/wiki/Join_(SQL)

And yes, both statements are equivalent :-)

Gardenia

Answer 5

Yes, it's determined by the side of the JOIN operator the table appears on. Your two examples are indeed equivalent.

Authenticity

Your answer seems to contradict itself. You say the position relative to the operator is what determines which table is left and right but in my example they are swapped, and you say they are equivalent. – Counterfeit

Read carefully, please: I said the position relative to the JOIN operator, not the equality operator. – Authenticity

Answer 6

CREATE TABLE ORDERS (
ORDERID INT,
CUSTOMERID INT,
ORDERDATE DATE
);

INSERT INTO ORDERS VALUES (10123,10,DATE '16-08-20');
INSERT INTO ORDERS VALUES (10122,11,DATE '14-09-20');
INSERT INTO ORDERS VALUES (10121,12,DATE '10-10-20');

CREATE TABLE CUSTOMERS (
CUSTOMERID INT,
CUSTOMERNAME VARCHAR(20),
COUNTRY VARCHAR(20)
);

INSERT INTO CUSTOMERS VALUES (11 , 'BUDDHA','INDIA');
INSERT INTO CUSTOMERS VALUES (12 , 'JOHNWIK','UNITED STATES');
INSERT INTO CUSTOMERS VALUES (100, 'SERENA','UNITED KINGDOM');

discussing LEFT JOIN query:

select orders.orderid, customers.customername, orders.orderdate from orders inner join customers on orders.customerid = customers.customerid;

If you want to know exact left and right tables. From left to right the table attached with from is [left] and table attached with join is [right].

Happy Hacking !!!

Giroux