Working with Multiple Tables - SQL

Categories

Relationship Types

In SQL, relationships between tables are established using foreign keys which define a link between two tables based on a column or set of columns in one table that refers to the primary key column[s] in another table. These relationship types provide a way to model complex data structures and define how data is organized and related in a database. Below are several types of relationships that are commonly used in SQL.

- One-to-One Relationship -

  • In a one-to-one relationship, each row in one table is related exactly to one row in another table and vice versa.

  • This relationship is created by defining a foreign key in one table that references the primary key in another table.

  • Example: A table of employees will most likely have a one-to-one relationship with a table containing employee contact information.

- One-to-Many Relationship -

  • In a one-to-many relationship, each row in one table can be related to multiple rows in another table, but each row in the other table is only related to one row in the first table.

  • This relationship is created by defining a foreign key in the “many” table that references the primary key in the “one” table.

  • Example: A table of departments will most likely have a one-to-many relationship with a table of employees because each department can have multiple employees.

- Many-to-One Relationship -

  • In a many-to-one relationship (a reverse one-to-many relationship), multiple rows in one table can be related to one row in another table.

  • This relationship is created by defining a foreign key in the “one” table that references the primary key in the “many” table.

  • Example: A table of employees will most likely have a many-to-one relationship with a table of departments because multiple employees can be in a single department.

- Many-to-Many Relationship -

  • In a many-to-many relationship, multiple rows in one table can be related to multiple rows in another table.

  • This relationship is created by using an associative table, which contains foreign keys referencing the primary keys of both tables involved in the relationship.

  • Example: A table of employees will most likely have a many-to-many relationship with a table of projects because each employee can be working on multiple projects and each project can have multiple employees.

Join Operations

- INNER JOIN -

An INNER JOIN returns only the rows from both tables that satisfy the join condition.

Syntax:

SELECT column1, column2
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name

SELECT: Specify the columns that you want to retrieve from either in the tables involved in the join.

FROM: Identify the first table that you want to pull information from.

INNER JOIN: Identify the second table that you want to join to the first table in the FROM statement. The INNER JOIN only returns rows that have matching values in both tables based on the specified condition.

ON: Specify the condition that determines how these two tables are related. You specify the column from the first and second table and how they should be joined together.

Example:

SELECT e.first_name, e.department_id, d.name
FROM company.employees e
INNER JOIN company.departments d ON e.department_id = d.id

SELECT: We are selecting three columns: e.first_name, e.department_id, and d.name. These columns will be included in the data results.

FROM: We specify that we want to pull data from the company.employees table and give it an alias of e. The alias allows us to refer to the company.employees table using e.

INNER JOIN: We specify that we want to INNER JOIN the company.departments table, given the alias d, with the company.employees table.

ON: We specify that we want to combine the tables using the department_id column in the company.employees table and the id column in the company.departments table.

- LEFT JOIN -

A LEFT JOIN returns all of the rows from the left table and only the matching rows that satisfy the join condition from the right table. If there are no matching rows in the right table, NULL values are returned for the columns on the right table.

Syntax:

SELECT column1, column2
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name

SELECT: Specify the columns that you want to retrieve from either in the tables involved in the join.

FROM: Identify the first table that you want to pull information from.

LEFT JOIN: Identify the second table that you want to join to the first table in the FROM statement. The LEFT JOIN returns all the rows from table1 and only the matching rows from the table2.

ON: Specify the condition that determines how these two tables are related. You specify the column from the first and second table and how they should be joined together.

Example:

SELECT e.first_name, e.department_id, d.name
FROM company.employees e
LEFT JOIN company.departments d ON e.department_id = d.id

SELECT: We are selecting three columns: e.first_name, e.department_id, and d.name. These columns will be included in the data results.

FROM: We specify that we want to pull data from the company.employees table and give it an alias of e. The alias allows us to refer to the company.employees table using e.

LEFT JOIN: We specify that we want to LEFT JOIN the company.departments table, given the alias d, with the company.employees table.

ON: We specify that we want to combine the tables using the department_id column in the company.employees table and the id column in the company.departments table.

- RIGHT JOIN -

A RIGHT JOIN returns all of the rows from the right table and only the matching rows that satisfy the join condition from the left table. If there are no matching rows in the left table, NULL values are returned for the columns on the left table.

Syntax:

SELECT column1, column2
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name

SELECT: Specify the columns that you want to retrieve from either in the tables involved in the join.

FROM: Identify the first table that you want to pull information from.

RIGHT JOIN: Identify the second table that you want to join to the first table in the FROM statement. The RIGHT JOIN returns all the rows from table2 and only the matching rows from the table1.

ON: Specify the condition that determines how these two tables are related. You specify the column from the first and second table and how they should be joined together.

Example:

SELECT e.first_name, e.department_id, d.name
FROM company.employees e
RIGHT JOIN company.departments d ON e.department_id = d.id

SELECT: We are selecting three columns: e.first_name, e.department_id, and d.name. These columns will be included in the data results.

FROM: We specify that we want to pull data from the company.employees table and give it an alias of e. The alias allows us to refer to the company.employees table using e.

RIGHT JOIN: We specify that we want to RIGHT JOIN the company.departments table, given the alias d, with the company.employees table.

ON: We specify that we want to combine the tables using the department_id column in the company.employees table and the id column in the company.departments table.

- CROSS JOIN -

A CROSS JOIN combines every row from the first table with every row from the second table, resulting in a new table that has every possible combinations of rows from both of the tables. This is known as a Cartesian product of the two tables.

Syntax:

SELECT column1, column2
FROM table1
CROSS JOIN table2

SELECT: Specify the columns that you want to retrieve from either in the tables involved in the join.

FROM: Identify the first table that you want to pull information from.

CROSS JOIN: Identify the second table that you want to join to the first table in the FROM statement. The CROSS JOIN returns the result of combining each row from table1 with every row from table2.

NOTE: For the CROSS JOIN operator we DO NOT need to specify the condition that determines how these two tables are related like we had to do for all of the other JOINs.

Example:

SELECT e.first_name, e.department_id, d.name
FROM company.employees e
CROSS JOIN company.departments d

SELECT: We are selecting three columns: e.first_name, e.department_id, and d.name. These columns will be included in the data results.

FROM: We specify that we want to pull data from the company.employees table and give it an alias of e. The alias allows us to refer to the company.employees table using e.

CROSS JOIN: We specify that we want to CROSS JOIN the company.departments table, given the alias d, with the company.employees table.

NOTE: There were more results but they didn’t fit in the screenshot.

Nested Joins

- Nested SELECT Queries -

In the SELECT statement you can use a nested query to retrieve data from another table or subquery and include it as a column in the results output.

Syntax:

SELECT column1, column2, (SELECT column3 FROM table2 WHERE condition) AS nested_column
FROM table1

In the syntax, the nested SELECT query (SELECT column3 FROM table2 WHERE condition) is embedded within the outer SELECT query. The result of the nested query will be returned as a column named nested_column, because it was assigned an alias, in the outer query’s result output.

Example:

SELECT
first_name,
job_title,
(SELECT id FROM company.departments WHERE name = 'Research') AS nested_column
FROM company.employees

In this example, the SQL query returns the first_name and job_title columns from the company.employees table. The query also includes a Nested SELECT query within the SELECT statement to return the id of the department named ‘Research’ from the company.departments table. The Nested query is aliased as nested_column and returned as part of the result output.

- Nested FROM Queries -

In the FROM statement you can use a nested query to treat the result of another query as a temporary table, which you can also join with other tables.

Syntax:

SELECT t1.column1, t2.column2
FROM (SELECT * FROM table1 WHERE condition) AS t1
INNER JOIN table2 AS t2 ON t1.column3 = t2.column3

In the syntax, the nested SELECT query (SELECT * FROM table1 WHERE condition), which is aliased as t1, is embedded in the FROM statement. Temporary table t1 is then joined with table2, which is aliased as t2, in the outer query.

Example:

SELECT e.first_name, d.name
FROM (SELECT * FROM company.employees WHERE id = 101) AS e
INNER JOIN company.departments AS d ON e.department_id = d.id

In this example, the SQL query returns the first_name from the nested SELECT query (SELECT * FROM company.employees WHERE id = 101), which is aliased as e, in the FROM statement. Then an INNER JOIN is performed between e and the company.departments table, which is aliased as d. Finally, we also return the name column from the d table which is the name of the department an individual works in.

- Nested WHERE Queries -

In the WHERE statement you can use a nested query to filter data based on the result of another query.

Syntax:

SELECT column1
FROM table1
WHERE column2 IN (SELECT column3 FROM table2 WHERE condition)

In the syntax, the nested SELECT query (SELECT column3 FROM table2 WHERE condition) returns a lost of values from table2 based on the specified condition. The outer query then filters the rows from table1 where column2 matches any of the values turned by the nested query.

Example:

SELECT first_name
FROM company.employees
WHERE department_id IN (SELECT id FROM company.departments WHERE id > 2)

In this example, the SQL query returns the first_name column from the company.employees table. It filters the rows based on the condition in the WHERE statement, which checks if the department_id of each employee is present in the nested query (SELECT id FROM company.departments WHERE id > 2). The nested query returns the id column from the company.departments table where the id is greater than 2.

- Nested HAVING Queries -

In the HAVING statement you can use a nested query to filter groups of rows based on specified conditions. While nested HAVING statements aren’t common, you can use them to perform more complex filtering.

Syntax:

SELECT column1 AGGREGATE_FUNCTION(column2)
FROM table1
GROUP BY column1
HAVING condition1 AND column1 IN (SELECT column3 FROM table2 WHERE condition2)

In the syntax, we are selecting column1 and applying an aggregate function to column2, which is grouped by column1. The nested SELECT query (SELECT column3 FROM table2 WHERE condition2) in the HAVING statement retrieves data from table2 based on certain conditions and those results are used to filter the grouped results in the outer query.

Example:

SELECT department_id, job_title, AVG(salary) AS avg_salary
FROM company.employees
GROUP BY department_id, job_title
HAVING avg_salary > 110000
AND department_id IN (SELECT id FROM company.departments WHERE id > 2)

In this example, the SQL query selects the department_id and job_title columns from the company.employees table, along with the average salary calculated using the AVG() function and aliased as avg_salary. The data is then grouped by department_id and job_title and the HAVING statement filters the grouped results to only include groups with an average salary greater than 110000 and belonging to a department with an id greater than 2. The results output includes the department_id, job_title, and avg_salary of employee groups that meet this criteria.