JOINS
- Inner joins
An inner join is used to combine rows from two or more tables based on a common value. The syntax for an inner join is as follows:
SELECT column_name, column_name
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name;
The table_name1 and table_name2 specifies the names of the tables that you want to join. The column_name specifies the column that you want to join the tables on.
For example, the following inner join will combine the rows from the customers table and the orders table based on the customer_id column:
SELECT customers.name, customers.email, orders.order_id, orders.order_date
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;
This will return all of the rows from the customers table that have a matching row in the orders table.
- Outer Joins
An outer join is used to combine rows from two or more tables, even if there is no common value. There are three types of outer joins: left outer joins, right outer joins, and full outer joins.
- Left Outer Joins
A left outer join returns all of the rows from the left table, even if there is no matching row in the right table. The syntax for a left outer join is as follows:
SELECT column_name, column_name
FROM table_name1
LEFT OUTER JOIN table_name2
ON table_name1.column_name = table_name2.column_name;
For example, the following left outer join will return all of the rows from the customers table, even if there is no matching row in the orders table:
SELECT customers.name, customers.email, orders.order_id, orders.order_date
FROM customers
LEFT OUTER JOIN orders
ON customers.customer_id = orders.customer_id;
This will return all of the rows from the customers table, and any matching rows from the orders table. The rows from the customers table that have no matching rows in the orders table will have null values in the order_id and order_date columns.
- Right Outer Joins
A right outer join returns all of the rows from the right table, even if there is no matching row in the left table. The syntax for a right outer join is as follows:
SELECT column_name, column_name
FROM table_name1
RIGHT OUTER JOIN table_name2
ON table_name1.column_name = table_name2.column_name;
For example, the following right outer join will return all of the rows from the orders table, even if there is no matching row in the customers table:
SELECT customers.name, customers.email, orders.order_id, orders.order_date
FROM customers
RIGHT OUTER JOIN orders
ON customers.customer_id = orders.customer_id;
This will return all of the rows from the orders table, and any matching rows from the customers table. The rows from the orders table that have no matching rows in the customers table will have null values in the name and email columns.
- Full Outer Joins
A full outer join returns all of the rows from both tables, even if there is no matching row in the other table. The syntax for a full outer join is as follows:
SELECT column_name, column_name
FROM table_name1
FULL OUTER JOIN table_name2
ON table_name1.column_name = table_name2.column_name;
For example, the following full outer join will return all of the rows from the customers table and the orders table, even if there is no matching row in the other table:
SELECT customers.name, customers.email, orders.order_id, orders.order_date
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id;
SELECT customers.name, customers.email, orders.order_id, orders.order_date
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id;
This will return all of the rows from both tables, and any rows that have no matching rows in the other table will have null values in the corresponding columns.
- SELF JOINS
A self join is a join that joins a table to itself. This is usually done by joining a table to itself just once within a SQL query, but it is possible to do so multiple times within the same query.
Self joins are often used to find relationships between rows in the same table. For example, you could use a self join to find all employees who have the same manager.
The syntax for a self join is as follows:
SELECT column_name, column_name
FROM table_name
AS t1
INNER JOIN table_name
AS t2
ON t1.column_name = t2.column_name;
The table_name specifies the name of the table that you want to join to itself. The AS keyword is used to give the table a alias. The column_name specifies the column that you want to join the tables on.
For example, the following self join will find all employees who have the same manager:
SELECT employee_id, name, manager_id, manager_name
FROM employees
AS e1
INNER JOIN employees
AS e2
ON e1.manager_id = e2.employee_id;
This will return all of the rows from the employees table, and any rows that have the same manager_id will be joined together.
Self joins can be used to find a variety of relationships between rows in a table. They are a powerful tool that can be used to analyze data in a variety of ways.