JOINS

• The SQL tool that allows us to construct a relationship between objects.
• A join shows a result set, containing fields derived from two or more tables.
• We must find a related column from the two tables that contain the same type of data.
• We will be free to add columns from these two tables to our output the columns you use to relate tables must represent the same object, such as id.
• The tables you are considering need not be logically adjacent

Types of joins
INNER JOIN = JOIN
LEFT JOIN = LEFT OUTER JOIN
RIGHT JOIN = RIGHT OUTER JOIN
CROSS JOIN

INNER JOIN
Extract only records in which the values in the related columns match. Null values, or values appearing in just one of the two tables and not appearing in the other, are not displayed.
Only non-null matching values are in play. And what if such matching values did not exist?
Simply, the result set will be empty. There will be no link between the two tables.
Syntax
SELECT
table_1.column_name(s), table_2.column_name(s)
FROM
table_1
JOIN

table_2 ON table_1.column_name = table_2.column_name;
LEFT JOIN
All matching values of the two tables and all values from the left table that match no values from the right table.
When working with left joins, the order in which you join tables matters.
Left joins can deliver a list with all records from the left table that do not match any rows from the right table.
Syntax
SELECT
table_1.column_name(s), table_2.column_name(s)
FROM
table_1
LEFT JOIN

table_2 ON table_1.column_name = table_2.column_name;
RIGHT JOIN
Their functionality is identical to LEFT JOINs, with the only difference being that the direction of the operation is inverted.
When applying a RIGHT JOIN, all the records from the right table will be included in the result set
values from the left table will be included only if their linking column contains a value coinciding, or matching, with a value from the linking column of the right table
Syntax
SELECT
table_1.column_name(s), table_2.column_name(s)
FROM
table_1
RIGHT  JOIN

table_2 ON table_1.column_name = table_2.column_name;

Note: LEFT and RIGHT joins are perfect examples of one to many relationships
UNION ALL
Used to combine a few SELECT statements in a single output you can think of it as a tool that allows you to unify tables. We have to select the same number of columns from each table.
These columns should have the same name, should be in the same order, and should contain related data types.
SQL SYNTAX
SELECT
N columns
FROM
table_1
UNION ALL SELECT
N columns
FROM
table_2;
Join more than two tables
SELECT
    e.first_name,
    e.last_name,
    e.hire_date,
    m.from_date,
    d.dept_name
FROM
    employees e
        JOIN
    dept_manager m ON e.emp_no = m.emp_no
        JOIN
    departments d ON m.dept_no = d.dept_no;
Here employees, dept_manager and departments.

UNION vs UNION ALL
When uniting two identically organized tables

• UNION displays only distinct values in the output whereas UNION ALL retrieves the duplicates as well.
• UNION uses more MySQL resources (computational power and storage space) whereas UNION ALL uses less MySQL resources.

Looking for better results: use UNION
Seeking to optimize performance: opt for UNION ALL
SUBQUERY

• Queries embedded in a query
• subqueries = inner queries = nested queries = inner select
• They are part of another query, called an outer query(outer select) a subquery should always be placed within parentheses.
• The SQL engine starts by running the inner query then it uses its returned output, which is intermediate, to execute the outer query.
• The subquery may return a single value(a scalar), single row, single column, or an entire table
• you can have a lot more than one subquery in your outer query it is possible to nest inner queries within other inner queries
• In that case, the SQL engine would execute the innermost query first, and then each subsequent query, until it runs the outermost query last

Select the second highest salary of the employee.

select e1.* from employee e1 where 1 = (select count(distinct salary) from employee e2 where e2.salary>e1.salary);
Next Topic: Stored Routines