SQL - Day 3

Okay, so today we'll learn about the most important concept of SQL, i.e. JOINS

It helps to combine data between tables.

SELECT m.name, g.genre from movies m JOIN movies_genres g ON m.id=g.movie_id LIMIT 20; //m and g are aliases

NATURAL JOIN:

If column name is same between two tables - SQL automatically joins these two tables on these columns

SELECT * FROM T1 JOIN T2; //no need to write "on columns" because they are same

OR

SELECT * FROM T1 JOIN T2 USING C1;

INNER JOIN:

Returns common values between two tables

SELECT * FROM T1 JOIN T2 ON T1.C1=T2.C4

LEFT JOIN, RIGHT JOIN AND FULL JOINS are explained in the picture above.

Note: JOINS are computationally expensive ( use Indexing to mitigate this)

SUB-QUERIES

Inner-most queries executed first.

Operators like: IN, NOT IN, EXISTS, NOT EXISTS, ANY (like OR: at least 1 subquery meet all conditions), ALL (like AND: all subqueries meet all conditions)

SELECT * FROM movies where rankscore>=ALL (SELECT MAX(rankscore) FROM movies);

//The sub-query returns one value currently, but in a situation where it returns multiple values - all movies will be displayed with rankscore >=all values in the sub-query. Get it?

Note: Sub-queries are slower than JOINS but they are easier to read

CORRELATED SUB-QUERIES

They're basically nested queries.

Read on Wiki page:

https://en.wikipedia.org/wiki/Correlated_subquery#:~:text=In%20a%20SQL%20database%20query,query%2C%20it%20can%20be%20slow.

Day 3 done, yay!