Suppose I have three tables here one of them is a Country table second one is a city table and the third one is a customer table, and I am writing a query here like this

SELECT country.country_name_eng, city.city_name, customer.customer_name
FROM Country
LEFT JOIN city ON city.country_id = country.id
LEFT JOIN customer ON customer.city_id = city.id

I want to know here who is the left table of the customer table and who is the left table of the city table as well as who is the right table of the country table and who is the right table of the customer table

CodePudding user response：

'Left' is always the table at the left of the JOIN keyword, no matter if left, right, inner or full outer join, analogously 'right'.

Left and right joins, though, have different semantics:

With a normal (inner) join a data row is only added if there's a matching entry in both tables, e.g. if you JOIN ON l.id = r.id then if l contains an id with value 7 a row for is only added if there's a matching id with value 7 in r as well (and vice versa).

A left (outer) join instead adds a row for id 7 in l even if there's no matching id in r – then the corresponding columns for table r are filled with nulls.

Analogously a right (outer) join, all entries of r are added with nulls for l's columns if no matching entry found.

A full outer join is a combination of left and right outer join, any entry of l and r is used with nulls on either side for the counter part if none exists.

In your concrete query you'd list all countries even if there don't exist any cities – and if they do exist, for each country all of them even if there don't exist any customers there. In the latter case the columns for the customer are all filled with null values, and if no city exists either, additionally all city columns.