CODEWITHSUNDEEP
sql
Pranjalakg
Pranjalakg

Reputation: 7

How can i work with multiple joins in sql?

Working on multiple joins i am not able to understand the working.

I am writing multiple joins using left join and right join.

SELECT cities.name AS city
     , urbanarea_pop
     , countries.name AS country
     , indep_year
     , languages.name AS language
     , percent
FROM cities
     LEFT JOIN countries
            ON cities.country_code = countries.code
     LEFT JOIN languages
            ON countries.code = languages.code
ORDER BY city, language;




SELECT cities.name AS city
     , urbanarea_pop
     , countries.name AS country
     , indep_year
     , languages.name AS language
     , percent
FROM languages
     RIGHT JOIN countries
             ON languages.code = countries.code
     RIGHT JOIN cities
             ON countries.code = cities.country_code
ORDER BY city, language;

I am getting same results with left joins and right joins. I am not able to understand how it is working.

Upvotes: 1

Views: 119

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

When you write:

FROM cities ci LEFT JOIN
     countries co
     ON ci.country_code = co.code LEFT JOIN
     languages l
     ON co.code = l.code

You are saying:

  • Keep all rows in cities. Period.
  • Non-matching rows in countries will have NULL values.
  • Non-matching rows in languages will have NULL values.

When you write:

FROM languages l RIGHT JOIN
     countries co
     ON l.code = co.code RIGHT JOIN
     cities ci
     ON co.code = ci.country_code

You are saying:

  • Keep all rows in countries even if they have no matching row in languages.
  • Keep all rows in cities even if there is no matching row in countries.

Under most circumstances, this is the same thing. There are some edge cases where you have a country with a language but no city, but well-formed data models don't have this problem.

I strongly advise you to use LEFT JOIN for two reasons:

  1. Cognitively, it is easier to follow "keep everything in the first table [which I have already know]" rather than "keep everything in the last table [and I don't know what that is yet as I read the query!]"
  2. SQL implicitly adds parentheses in the order the tables are declared, so "A JOIN B JOIN C" is interpreted as (A JOIN B) JOIN C. This is the reason why the LEFT JOIN and RIGHT JOIN versions are not always the same (but they are the same under most circumstances).

Also, use table aliases and qualify all your column references.

Upvotes: 0

Amit Sakare
Amit Sakare

Reputation: 214

It might be possible that both your languages and countries tables have the same records, please check the records in both tables, otherwise, I don't think there is any issue with this approach.

Upvotes: 1

GMc
GMc

Reputation: 1774

A left join returns every record in the left table and matching records (based upon your join conditions) from the right table. Records that appear in the left but not the right will have nulls placed into the columns that would otherwise have come from the right table.

A right join is similar except that it returns every record from the right table and only those that match from the left table. Again records that are not present on the left will be returned as nulls.

Consider the following two tables....

Table A

id
-
1
2

and table B

id
-
1
3

if you run 

select a.*, b.*
from a LEFT outer join b on
   a.id = b.id

you will get:

a.id    b.id
1       1
2       NULL

Similarly a right outer join will return the following:

a.id    b.id
1       1
NULL    3

For reference a FULL OUTER JOIN will return the following:

a.id    b.id
1       1
2       NULL
NULL    3

Since you get the same result irrespective of the "direction" of your outer join, this means that all of the "Key" values in your left tables are aligned with the "Key" values in your right one.

Upvotes: 0

Razvan Dragos
Razvan Dragos

Reputation: 624

If you use LEFT JOIN keyword, you will get all records from the LEFT table(cities) and the matched records from the RIGHT table(s) (In your case, countries and languages).

Then, if you use RIGHT JOIN keyword, you will get all the records from the right table(s) (In your case, countries and cities) and the matched records from the LEFT table (languages).

So, the same result may be caused by the fact that languages and countries could have the same records.

Upvotes: 0

Related Questions