Joins & Union

# Join statement
# Inner joins, Full/Left/Right outer joins

# Join/Innner Join
SELECT * 
FROM EmployeeDemographics
JOIN EmployeeSalary
ON EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID

# Full Outer Join
SELECT * 
FROM EmployeeDemographics
FULL OUTER JOIN EmployeeSalary
ON EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID

# Upon research, MYSQL doesn't support "Full Outer Join". In order to do a Full Outer Join, a combination of Left & Right Join would be needed

# The query below will returns data based on the JOIN EmployeeID, without returning any duplicates. 
SELECT * 
FROM EmployeeDemographics
LEFT JOIN EmployeeSalary ON EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID
UNION ALL
SELECT * 
FROM EmployeeDemographics
RIGHT JOIN EmployeeSalary ON EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID


# The query below using will return duplicate rows
SELECT * 
FROM EmployeeDemographics
LEFT JOIN EmployeeSalary ON EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID
UNION ALL
SELECT * 
FROM EmployeeDemographics
RIGHT JOIN EmployeeSalary ON EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID
WHERE EmployeeDemographics.EmployeeID IS NULL

# Left Join
SELECT * 
FROM EmployeeDemographics
LEFT JOIN EmployeeSalary
ON EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID


# Right Join
SELECT * 
FROM EmployeeDemographics
RIGHT JOIN EmployeeSalary
ON EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID

#Difference between Join & Union. Join merges tables based on a common column. Union lets you select all data in both tables and put it into one output.
# Union combines data in both tables, while removing duplicates.
SELECT * 
FROM EmployeeDemographics
UNION
SELECT * 
FROM WareHouseEmployeeDemographics

# Union All combines data from both tables while still showing duplicates.
SELECT * 
FROM EmployeeDemographics
UNION ALL
SELECT * 
FROM WareHouseEmployeeDemographics