JOIN & Operator

Inner join

Select only intersection area of left and right data set.

SELECT * FROM left_table INNER JOIN right_table
ON left_table.column_name = right_table.column_name 
WHERE condition

left join

Select all data from left taable and macth data in right_table

SELECT * FROM left_table LEFT JOIN right_table
ON left_table.column_name = right_table.column_name 
WHERE condition

Right join

Select all data from right table and macth data in left_table

SELECT * FROM left_table RIGHT JOIN right_table
ON left_table.column_name = right_table.column_name 
WHERE condition

Full outer join

Get all data from left table and right table

SELECT * FROM left_table FULL OUTER JOIN right_table
ON left_table.column_name = right_table.column_name 
WHERE condition

---

Union

combine the resut from two table and return only unique value

SELECT * FROM table_1
WHERE condition
UNION
SELECT * FROM table_2
WHERE condition

Union all

combine the resut from two table and return all record

SELECT * FROM table_1
WHERE condition
UNION ALL
SELECT * FROM table_2
WHERE condition

Intersect

combine the resut from two table but return only row that have same value

SELECT * FROM table_1
WHERE condition
INTERSECT
SELECT * FROM table_2
WHERE condition

EXCEPT

select value that is only in table 1

SELECT * FROM table_1
WHERE condition
EXCEPT
SELECT * FROM table_2
WHERE condition