SQL Serise Part II (Basic Syntax

JOIN

we need to know the ON is uesed between two tables' FK and PK
FK: Foreign Key
PK: Primary Key

SELECT TABLE1.*, TABLE2.*
FROM TABLE1
JOIN TABLE2
ON TABLE1.id = TABLE2.id;
# equal to
SELECT * 
FROM TABLE1, TABLE2 
WHERE TABLE1.id = TABLE2.id;

# we also can join much more tables and more condition to show our data
SELECT a.name, b.name, c.total_price/c.total
FROM a
JOIN d ON d.Foreign_Key = a.id
JOIN a ON a.Foreign_Key = d.id
JOIN b ON b.Foreign_Key = a.id;

# LEFT JOIN & RIGHT JOIN
LEFT JOIN will get all rows in FROM table, RIGHT JOIN will get all rows in JOIN table
SELECT a.main_id, a.main_name, b.aux_namme
FROM a
LEFT JOIN b
ON a.main_id = b.main_id

UNION

# the UNION operator is used to combine the result-set of all SELECT statements
SELECT name FROM table1
UNION
SELECT name FROM table2;
# To allow duplicate values, use UNION ALL
SELECT name FROM table1
UNION ALL
SELECT name FROM table2;