Day 23: SQL Joins

Joining Tables

Joining tables is one of the fundamental operations in relational databases. It allows us to combine data from multiple tables into a single result set. There are different types of joins in SQL, including inner join, left join, right join, and full outer join.

Here is an overview of the different types of joins:

1. Inner join: Returns only the rows that have matching values in both tables.
2. Left join: Returns all the rows from the left table and the matching rows from the right table. If there are no matching rows in the right table, the result will contain NULL values for the right table columns.
3. Right join: Returns all the rows from the right table and the matching rows from the left table. If there are no matching rows in the left table, the result will contain NULL values for the left table columns.
4. Full outer join: Returns all the rows from both tables, and NULL values for the columns where there is no match.

Inner Join

An inner join is a type of join used in SQL to combine rows from two or more tables based on a related column between them. It returns only those rows from both tables where the join condition is true.

Inner Join Syntax:

The basic syntax of an inner join is as follows:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

In this syntax:

• SELECT: specifies the columns to be returned in the result set.
• FROM: specifies the first table from which the data will be selected.
• INNER JOIN: specifies the type of join to be used, which in this case is an inner join.
• table2: specifies the name of the second table to be joined.
• ON: specifies the condition for joining the two tables.

Inner Join Example:

Consider the following two tables, employees and departments:

employees:
+----+---------+------------+
| id | name    | department |
+----+---------+------------+
| 1  | Alice   | 1          |
| 2  | Bob     | 2          |
| 3  | Charlie | 1          |
| 4  | David   | 3          |
| 5  | Eve     | 1          |
+----+---------+------------+

departments:
+----+------------+
| id | name       |
+----+------------+
| 1  | Accounting |
| 2  | Sales      |
| 3  | Marketing  |
+----+------------+

We can use an inner join to combine these tables based on the department id:

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
ON employees.department = departments.id;

This query will return the following result:

+---------+------------+
| name    | name       |
+---------+------------+
| Alice   | Accounting |
| Bob     | Sales      |
| Charlie | Accounting |
| David   | Marketing  |
| Eve     | Accounting |
+---------+------------+

This result set shows the name of each employee and their department.

Conclusion:

Inner joins in SQL allow us to combine data from multiple tables based on a related column. They are a powerful tool for working with relational databases and are used extensively in modern applications. By understanding how to use inner joins, you can perform complex queries and extract valuable insights from your data.

Outer Join

An outer join is a type of join in SQL that returns all the rows from one table and the matching rows from another table. In cases where there is no matching row from the second table, NULL values are returned.

There are three types of outer joins in SQL:

1. Left outer join
2. Right outer join
3. Full outer join

Let’s take a look at each of these in more detail.

Left outer join

A left outer join returns all the rows from the left table and the matching rows from the right table. If there are no matching rows from the right table, NULL values are returned.

Here is the syntax for a left outer join:

SELECT *
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

In this example, table1 is the left table and table2 is the right table. The ON clause is used to specify the join condition.

Right outer join

A right outer join returns all the rows from the right table and the matching rows from the left table. If there are no matching rows from the left table, NULL values are returned.

Here is the syntax for a right outer join:

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

In this example, table2 is the right table and table1 is the left table.

Full outer join

A full outer join returns all the rows from both tables. If there are no matching rows in one of the tables, NULL values are returned.

Here is the syntax for a full outer join:

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

In this example, both table1 and table2 are included in the result set. The ON clause is used to specify the join condition.

Outer joins are useful when you want to include all the rows from one table in the result set, regardless of whether there are matching rows in the other table.

Cross Join

A cross join, also known as a Cartesian product, is a type of join that returns all possible combinations of rows from two or more tables. It does not use a join condition like inner or outer joins, but instead simply combines each row from one table with every row from the other table.

To perform a cross join, you can use the CROSS JOIN keyword in your SQL query, followed by the names of the tables you want to join. For example:

SELECT *
FROM table1
CROSS JOIN table2;

This will return all possible combinations of rows from table1 and table2.

It’s worth noting that cross joins can produce very large result sets, especially when joining large tables. Therefore, they should be used with caution and only when necessary.

Here is an example of a cross join in action:

Assume we have two tables, table1 and table2, with the following data:

table1:

id	name
1	Alice
2	Bob
3	Carol

table2:

id	age
1	25
2	30
3	35

To perform a cross join of these tables, we can use the following SQL query:

SELECT *
FROM table1
CROSS JOIN table2;

This will produce the following result set:

id	name	id	age
1	Alice	1	25
1	Alice	2	30
1	Alice	3	35
2	Bob	1	25
2	Bob	2	30
2	Bob	3	35
3	Carol	1	25
3	Carol	2	30
3	Carol	3	35

Self Join

A self join is a type of join where a table is joined with itself. This is useful when you need to compare rows within the same table, or when you need to group related rows.

Creating a table for self join demonstration:

We will first create a table to demonstrate self joins in SQL. Here’s the SQL code to create a table called employees:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  manager_id INT,
  department VARCHAR(50)
);

INSERT INTO employees (id, name, manager_id, department) VALUES
(1, 'John Smith', NULL, 'Sales'),
(2, 'Jane Doe', 1, 'Sales'),
(3, 'Bob Johnson', 1, 'Sales'),
(4, 'Sarah Thompson', NULL, 'Marketing'),
(5, 'Mike Davis', 4, 'Marketing');

This table has five columns: id, name, manager_id, and department. The id column is the primary key, which means that it is a unique identifier for each row in the table. The name column contains the name of each employee, the manager_id column contains the ID of the employee’s manager, and the department column contains the name of the employee’s department.

Self Join Syntax:

To perform a self join in SQL, you need to join a table with itself using an alias. The syntax for a self join is as follows:

SELECT *
FROM table1 t1
JOIN table1 t2 ON t1.column_name = t2.column_name;

In this syntax, table1 is the name of the table that you want to join, and t1 and t2 are aliases for the table. The column_name is the name of the column that you want to join on. The SELECT statement can be replaced with the column names that you want to select.

Self Join Example:

Let’s use the employees table to demonstrate a self join. Suppose we want to find the names of all employees and their managers. We can use a self join to accomplish this. Here’s the SQL code:

SELECT e1.name AS employee_name, e2.name AS manager_name
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;

In this SQL code, we have used two aliases e1 and e2 for the employees table. We have joined the table on e1.manager_id = e2.id, which means that we are matching the ID of the employee’s manager with the ID of another employee. We are selecting the name column from both tables, and we are aliasing the columns as employee_name and manager_name.

Output:

The output of the above SQL code will be:

+----------------+--------------+
| employee_name  | manager_name |
+----------------+--------------+
| Jane Doe       | John Smith   |
| Bob Johnson    | John Smith   |
| Mike Davis     | Sarah Thompson |
+----------------+--------------+

This result shows the names of all employees and their corresponding manager names.

Conclusion:

Self joins are a powerful tool in SQL that can be used to compare rows within the same table. In this lesson, we covered the syntax and example of a self join, using the employees table. Self joins can be used in various scenarios like comparing rows within the same table or grouping related rows.

Using different types of joins to combine data from multiple tables

Using multiple joins in a single SQL query allows you to combine data from multiple tables with more complex conditions. To do this, you can use the JOIN keyword to combine two tables, and then join the result with another table using the same keyword. The order in which the tables are joined can affect the results of the query, so it’s important to be careful when using multiple joins.

Here’s an example query that uses multiple joins:

SELECT *
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN order_items ON orders.order_id = order_items.order_id;

In this example, the query selects all columns from the customers table, joins it with the orders table on the customer_id column, and then joins the result with the order_items table on the order_id column.

It’s important to note that you can also use different types of joins in the same query, such as INNER JOIN, LEFT JOIN, and RIGHT JOIN. To specify the type of join, you can use the respective keywords after the table name. For example:

SELECT *
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN order_items ON orders.order_id = order_items.order_id;

This query combines the customers and orders tables using an inner join, and then combines the result with the order_items table using a left join.

When using multiple joins, it’s important to ensure that the join conditions are correct and that you’re getting the expected results. You can use the WHERE clause to filter the results further based on specific conditions.

Use of aliases to simplify table names

In SQL, aliases are used to give a temporary name to a table or a column in a table. This can be helpful for simplifying complex queries, making it easier to read and understand the code.

To create an alias for a table, you can use the AS keyword followed by the new name. For example:

SELECT *
FROM mytable AS t
WHERE t.column1 = 'value'

In this example, the table mytable is given the alias t.

To create an alias for a column in a table, you can use the AS keyword followed by the new name. For example:

SELECT t.column1 AS c1, t.column2 AS c2
FROM mytable AS t
WHERE t.column1 = 'value'

In this example, the column column1 in the mytable table is given the alias c1, and the column column2 is given the alias c2.

Using aliases can simplify the table names and column names in your queries, making it easier to read and understand the code. It can also be useful when joining tables, as you may have to use the same table multiple times with different aliases to reference it properly.

Exercise:

Write SELECT statements using different types of joins to combine data from multiple tables