Code icon

The App is Under a Quick Maintenance

We apologize for the inconvenience. Please come back later

Cuantum Technologies logo
Dashboard
Perfil
Facturación
Full Access
Salir
Menu iconMenu iconLa Biblia de Python y SQL: Desde principiante hasta experto mundial
La Biblia de Python y SQL: Desde principiante hasta experto mundial
Arrow Icon
Chapter 14: Deep Dive into SQL Queries
14.1 Advanced Select Queries
14.2 Joining Multiple Tables
14.3 Aggregate Functions
14.4 Practical Exercises of Chapter 14: Deep Dive into SQL Queries
Comprar libro
You've learned this already. ✅
Click here to view the next lesson.

Chapter 14: Deep Dive into SQL Queries

14.2 Joining Multiple Tables

In SQL, JOIN clauses are used to combine rows from two or more tables based on a related column between them. There are various types of JOINs available in SQL:

  1. (INNER) JOIN: Returns records that have matching values in both tables.
  2. LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.
  3. RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.
  4. FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table.

INNER JOIN Keyword

The INNER JOIN keyword is used to combine data from two different tables using a common column. This is particularly useful when we want to retrieve data that exists in both tables. By using the INNER JOIN keyword, we can ensure that only the records with matching values in both tables are returned.

This can help us to better understand the relationships between different pieces of data and to gain insights that we might not have been able to see otherwise. Additionally, the INNER JOIN keyword is just one of many different types of joins that we can use to combine data from multiple tables. Other types of joins include LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN, each of which has its own unique properties and use cases.

By understanding the different types of joins that are available to us, we can ensure that we are using the right tool for the job and getting the most out of our data.

Syntax:

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

Example:

Consider we have two tables, Orders and Customers, with the following structure:

Orders:

OrderID | CustomerID | OrderAmount
--------|------------|------------
1       | 1          | 100
2       | 2          | 200
3       | 5          | 300
4       | 3          | 400

Customers:

CustomerID | Name  | Country
-----------|-------|---------
1          | Alex  | USA
2          | Bob   | UK
3          | Chris | France
4          | Dave  | Canada

An INNER JOIN selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matching entries in the "Customers" table, those records will be omitted from the result.

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderAmount
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Result:

OrderID | CustomerName | OrderAmount
--------|--------------|------------
1       | Alex         | 100
2       | Bob          | 200
3       | Chris        | 400

14.2.1 LEFT JOIN and RIGHT JOIN

LEFT JOIN Keyword

The LEFT JOIN keyword is a type of join that retrieves rows from the left table (table1) and the matching rows from the right table (table2). This means that if there is no match in the right table, the resulting value will be NULL.

It is important to note that LEFT JOIN is different from INNER JOIN, as the latter only returns rows that have matching data in both tables. LEFT JOIN, on the other hand, will still show all the rows from the left table even if there is no corresponding data in the right table.

This can be useful when working with data that has missing values or when you want to see all the data from one table regardless of whether there is matching data in the other table. Additionally, LEFT JOIN can be combined with other SQL statements such as WHERE, ORDER BY, and GROUP BY to further refine the results and obtain the desired output.

Syntax:

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

Example:

SELECT Customers.CustomerName, Orders.OrderAmount
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result:

CustomerName | OrderAmount
-------------|------------
Alex         | 100
Bob          | 200
Chris        | 400
Dave         | NULL

As you can see, the LEFT JOIN keyword returns all records from the left table (Customers), and the matched records from the right table (Orders). The result is NULL on the right side, when there is no match.

RIGHT JOIN Keyword

The RIGHT JOIN keyword is used to combine data from two tables, table1 and table2. This type of join returns all the rows from table2 and the matching rows from table1. If there is no match from table1, the result will be NULL on the left side. The RIGHT JOIN is often used when you want to include all the data from table2 and only the matching data from table1.

For example, let's say you have two tables: one containing information about employees (table1) and one containing information about departments (table2). You want to display a list of all the departments, even if there are no employees in some of them. The RIGHT JOIN can be used to get all the departments from table2 and only the matching data from table1 (the employees that belong to each department).

It's worth noting that RIGHT JOIN is not a commonly used type of join. In most cases, a LEFT JOIN is used instead. However, there are some situations where a RIGHT JOIN can be useful, such as when you need to display all the data from the second table and only matching data from the first table.

Syntax:

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

Example:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderAmount
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Result:

OrderID | CustomerName | OrderAmount
--------|--------------|------------
1       | Alex         | 100
2       | Bob          | 200
4       | Chris        | 400
NULL    | Dave         | NULL

14.2.2 FULL OUTER JOIN

When using SQL to join tables, the FULL OUTER JOIN keyword can be a useful tool. This keyword returns all records in both the left (table1) and right (table2) tables, even if there is no match between them.

This means that even if a record in one table does not have a corresponding match in the other table, it will still be included in the result set. The FULL OUTER JOIN keyword is especially helpful when you want to ensure that all the data from both tables is included in the query results, regardless of whether there is a match or not.

By using this keyword, you can avoid the risk of missing important information that may be present in one table but not the other.

Syntax:

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

Example:

SELECT Customers.CustomerName, Orders.OrderAmount
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result:

CustomerName | OrderAmount
-------------|------------
Alex         | 100
Bob          | 200
Chris        | 400
Dave         | NULL
Eve          | 500

In this example, the FULL OUTER JOIN keyword returns all records when there is a match in either the left (Customers) or the right (Orders) table records. It combines the results of both left and right outer joins and returns all (matched or unmatched) records.

Please note that not all database systems support the FULL OUTER JOIN keyword. For example, MySQL does not support FULL OUTER JOIN, but you can achieve the same result by combining LEFT JOIN and UNION.

14.2.3 UNION and UNION ALL

The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within the UNION must have the same number of columns, the columns must also have similar data types, and they must also be in the same order.

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

Syntax:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

For allowing duplicate values:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Example:

SELECT city FROM Customers
UNION
SELECT city FROM Suppliers
ORDER BY city;

This SQL statement would return all distinct cities from the "Customers" and the "Suppliers" table.

Example with UNION ALL:

SELECT city FROM Customers
UNION ALL
SELECT city FROM Suppliers
ORDER BY city;

This SQL statement would return all cities (duplicate also) from the "Customers" and the "Suppliers" table.

14.2.4 Subqueries

Subqueries (also known as inner queries or nested queries) are a tool for performing operations in multiple steps. In other words, it's a query within another SQL query. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. A subquery is usually added in the WHERE Clause of the SQL Statement.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator
    (SELECT column_name(s)
    FROM table_name
    WHERE condition);

For example, you can find the customers who are located in the same city as the supplier 'Exotic Liquid' with the following query:

SELECT CustomerName, ContactName, City
FROM Customers
WHERE City =
    (SELECT City
    FROM Suppliers
    WHERE SupplierName = 'Exotic Liquid');

This will return all the customer details who are located in the same city as 'Exotic Liquid'.

Subqueries can be a powerful tool in your SQL toolbox, allowing you to perform complex queries in a step-by-step manner, making your queries more readable and easier to debug.

In the next section, we will explore aggregation functions in SQL.

14.2 Joining Multiple Tables

In SQL, JOIN clauses are used to combine rows from two or more tables based on a related column between them. There are various types of JOINs available in SQL:

  1. (INNER) JOIN: Returns records that have matching values in both tables.
  2. LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.
  3. RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.
  4. FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table.

INNER JOIN Keyword

The INNER JOIN keyword is used to combine data from two different tables using a common column. This is particularly useful when we want to retrieve data that exists in both tables. By using the INNER JOIN keyword, we can ensure that only the records with matching values in both tables are returned.

This can help us to better understand the relationships between different pieces of data and to gain insights that we might not have been able to see otherwise. Additionally, the INNER JOIN keyword is just one of many different types of joins that we can use to combine data from multiple tables. Other types of joins include LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN, each of which has its own unique properties and use cases.

By understanding the different types of joins that are available to us, we can ensure that we are using the right tool for the job and getting the most out of our data.

Syntax:

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

Example:

Consider we have two tables, Orders and Customers, with the following structure:

Orders:

OrderID | CustomerID | OrderAmount
--------|------------|------------
1       | 1          | 100
2       | 2          | 200
3       | 5          | 300
4       | 3          | 400

Customers:

CustomerID | Name  | Country
-----------|-------|---------
1          | Alex  | USA
2          | Bob   | UK
3          | Chris | France
4          | Dave  | Canada

An INNER JOIN selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matching entries in the "Customers" table, those records will be omitted from the result.

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderAmount
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Result:

OrderID | CustomerName | OrderAmount
--------|--------------|------------
1       | Alex         | 100
2       | Bob          | 200
3       | Chris        | 400

14.2.1 LEFT JOIN and RIGHT JOIN

LEFT JOIN Keyword

The LEFT JOIN keyword is a type of join that retrieves rows from the left table (table1) and the matching rows from the right table (table2). This means that if there is no match in the right table, the resulting value will be NULL.

It is important to note that LEFT JOIN is different from INNER JOIN, as the latter only returns rows that have matching data in both tables. LEFT JOIN, on the other hand, will still show all the rows from the left table even if there is no corresponding data in the right table.

This can be useful when working with data that has missing values or when you want to see all the data from one table regardless of whether there is matching data in the other table. Additionally, LEFT JOIN can be combined with other SQL statements such as WHERE, ORDER BY, and GROUP BY to further refine the results and obtain the desired output.

Syntax:

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

Example:

SELECT Customers.CustomerName, Orders.OrderAmount
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result:

CustomerName | OrderAmount
-------------|------------
Alex         | 100
Bob          | 200
Chris        | 400
Dave         | NULL

As you can see, the LEFT JOIN keyword returns all records from the left table (Customers), and the matched records from the right table (Orders). The result is NULL on the right side, when there is no match.

RIGHT JOIN Keyword

The RIGHT JOIN keyword is used to combine data from two tables, table1 and table2. This type of join returns all the rows from table2 and the matching rows from table1. If there is no match from table1, the result will be NULL on the left side. The RIGHT JOIN is often used when you want to include all the data from table2 and only the matching data from table1.

For example, let's say you have two tables: one containing information about employees (table1) and one containing information about departments (table2). You want to display a list of all the departments, even if there are no employees in some of them. The RIGHT JOIN can be used to get all the departments from table2 and only the matching data from table1 (the employees that belong to each department).

It's worth noting that RIGHT JOIN is not a commonly used type of join. In most cases, a LEFT JOIN is used instead. However, there are some situations where a RIGHT JOIN can be useful, such as when you need to display all the data from the second table and only matching data from the first table.

Syntax:

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

Example:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderAmount
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Result:

OrderID | CustomerName | OrderAmount
--------|--------------|------------
1       | Alex         | 100
2       | Bob          | 200
4       | Chris        | 400
NULL    | Dave         | NULL

14.2.2 FULL OUTER JOIN

When using SQL to join tables, the FULL OUTER JOIN keyword can be a useful tool. This keyword returns all records in both the left (table1) and right (table2) tables, even if there is no match between them.

This means that even if a record in one table does not have a corresponding match in the other table, it will still be included in the result set. The FULL OUTER JOIN keyword is especially helpful when you want to ensure that all the data from both tables is included in the query results, regardless of whether there is a match or not.

By using this keyword, you can avoid the risk of missing important information that may be present in one table but not the other.

Syntax:

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

Example:

SELECT Customers.CustomerName, Orders.OrderAmount
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result:

CustomerName | OrderAmount
-------------|------------
Alex         | 100
Bob          | 200
Chris        | 400
Dave         | NULL
Eve          | 500

In this example, the FULL OUTER JOIN keyword returns all records when there is a match in either the left (Customers) or the right (Orders) table records. It combines the results of both left and right outer joins and returns all (matched or unmatched) records.

Please note that not all database systems support the FULL OUTER JOIN keyword. For example, MySQL does not support FULL OUTER JOIN, but you can achieve the same result by combining LEFT JOIN and UNION.

14.2.3 UNION and UNION ALL

The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within the UNION must have the same number of columns, the columns must also have similar data types, and they must also be in the same order.

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

Syntax:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

For allowing duplicate values:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Example:

SELECT city FROM Customers
UNION
SELECT city FROM Suppliers
ORDER BY city;

This SQL statement would return all distinct cities from the "Customers" and the "Suppliers" table.

Example with UNION ALL:

SELECT city FROM Customers
UNION ALL
SELECT city FROM Suppliers
ORDER BY city;

This SQL statement would return all cities (duplicate also) from the "Customers" and the "Suppliers" table.

14.2.4 Subqueries

Subqueries (also known as inner queries or nested queries) are a tool for performing operations in multiple steps. In other words, it's a query within another SQL query. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. A subquery is usually added in the WHERE Clause of the SQL Statement.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator
    (SELECT column_name(s)
    FROM table_name
    WHERE condition);

For example, you can find the customers who are located in the same city as the supplier 'Exotic Liquid' with the following query:

SELECT CustomerName, ContactName, City
FROM Customers
WHERE City =
    (SELECT City
    FROM Suppliers
    WHERE SupplierName = 'Exotic Liquid');

This will return all the customer details who are located in the same city as 'Exotic Liquid'.

Subqueries can be a powerful tool in your SQL toolbox, allowing you to perform complex queries in a step-by-step manner, making your queries more readable and easier to debug.

In the next section, we will explore aggregation functions in SQL.

14.2 Joining Multiple Tables

In SQL, JOIN clauses are used to combine rows from two or more tables based on a related column between them. There are various types of JOINs available in SQL:

  1. (INNER) JOIN: Returns records that have matching values in both tables.
  2. LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.
  3. RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.
  4. FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table.

INNER JOIN Keyword

The INNER JOIN keyword is used to combine data from two different tables using a common column. This is particularly useful when we want to retrieve data that exists in both tables. By using the INNER JOIN keyword, we can ensure that only the records with matching values in both tables are returned.

This can help us to better understand the relationships between different pieces of data and to gain insights that we might not have been able to see otherwise. Additionally, the INNER JOIN keyword is just one of many different types of joins that we can use to combine data from multiple tables. Other types of joins include LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN, each of which has its own unique properties and use cases.

By understanding the different types of joins that are available to us, we can ensure that we are using the right tool for the job and getting the most out of our data.

Syntax:

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

Example:

Consider we have two tables, Orders and Customers, with the following structure:

Orders:

OrderID | CustomerID | OrderAmount
--------|------------|------------
1       | 1          | 100
2       | 2          | 200
3       | 5          | 300
4       | 3          | 400

Customers:

CustomerID | Name  | Country
-----------|-------|---------
1          | Alex  | USA
2          | Bob   | UK
3          | Chris | France
4          | Dave  | Canada

An INNER JOIN selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matching entries in the "Customers" table, those records will be omitted from the result.

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderAmount
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Result:

OrderID | CustomerName | OrderAmount
--------|--------------|------------
1       | Alex         | 100
2       | Bob          | 200
3       | Chris        | 400

14.2.1 LEFT JOIN and RIGHT JOIN

LEFT JOIN Keyword

The LEFT JOIN keyword is a type of join that retrieves rows from the left table (table1) and the matching rows from the right table (table2). This means that if there is no match in the right table, the resulting value will be NULL.

It is important to note that LEFT JOIN is different from INNER JOIN, as the latter only returns rows that have matching data in both tables. LEFT JOIN, on the other hand, will still show all the rows from the left table even if there is no corresponding data in the right table.

This can be useful when working with data that has missing values or when you want to see all the data from one table regardless of whether there is matching data in the other table. Additionally, LEFT JOIN can be combined with other SQL statements such as WHERE, ORDER BY, and GROUP BY to further refine the results and obtain the desired output.

Syntax:

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

Example:

SELECT Customers.CustomerName, Orders.OrderAmount
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result:

CustomerName | OrderAmount
-------------|------------
Alex         | 100
Bob          | 200
Chris        | 400
Dave         | NULL

As you can see, the LEFT JOIN keyword returns all records from the left table (Customers), and the matched records from the right table (Orders). The result is NULL on the right side, when there is no match.

RIGHT JOIN Keyword

The RIGHT JOIN keyword is used to combine data from two tables, table1 and table2. This type of join returns all the rows from table2 and the matching rows from table1. If there is no match from table1, the result will be NULL on the left side. The RIGHT JOIN is often used when you want to include all the data from table2 and only the matching data from table1.

For example, let's say you have two tables: one containing information about employees (table1) and one containing information about departments (table2). You want to display a list of all the departments, even if there are no employees in some of them. The RIGHT JOIN can be used to get all the departments from table2 and only the matching data from table1 (the employees that belong to each department).

It's worth noting that RIGHT JOIN is not a commonly used type of join. In most cases, a LEFT JOIN is used instead. However, there are some situations where a RIGHT JOIN can be useful, such as when you need to display all the data from the second table and only matching data from the first table.

Syntax:

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

Example:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderAmount
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Result:

OrderID | CustomerName | OrderAmount
--------|--------------|------------
1       | Alex         | 100
2       | Bob          | 200
4       | Chris        | 400
NULL    | Dave         | NULL

14.2.2 FULL OUTER JOIN

When using SQL to join tables, the FULL OUTER JOIN keyword can be a useful tool. This keyword returns all records in both the left (table1) and right (table2) tables, even if there is no match between them.

This means that even if a record in one table does not have a corresponding match in the other table, it will still be included in the result set. The FULL OUTER JOIN keyword is especially helpful when you want to ensure that all the data from both tables is included in the query results, regardless of whether there is a match or not.

By using this keyword, you can avoid the risk of missing important information that may be present in one table but not the other.

Syntax:

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

Example:

SELECT Customers.CustomerName, Orders.OrderAmount
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result:

CustomerName | OrderAmount
-------------|------------
Alex         | 100
Bob          | 200
Chris        | 400
Dave         | NULL
Eve          | 500

In this example, the FULL OUTER JOIN keyword returns all records when there is a match in either the left (Customers) or the right (Orders) table records. It combines the results of both left and right outer joins and returns all (matched or unmatched) records.

Please note that not all database systems support the FULL OUTER JOIN keyword. For example, MySQL does not support FULL OUTER JOIN, but you can achieve the same result by combining LEFT JOIN and UNION.

14.2.3 UNION and UNION ALL

The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within the UNION must have the same number of columns, the columns must also have similar data types, and they must also be in the same order.

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

Syntax:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

For allowing duplicate values:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Example:

SELECT city FROM Customers
UNION
SELECT city FROM Suppliers
ORDER BY city;

This SQL statement would return all distinct cities from the "Customers" and the "Suppliers" table.

Example with UNION ALL:

SELECT city FROM Customers
UNION ALL
SELECT city FROM Suppliers
ORDER BY city;

This SQL statement would return all cities (duplicate also) from the "Customers" and the "Suppliers" table.

14.2.4 Subqueries

Subqueries (also known as inner queries or nested queries) are a tool for performing operations in multiple steps. In other words, it's a query within another SQL query. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. A subquery is usually added in the WHERE Clause of the SQL Statement.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator
    (SELECT column_name(s)
    FROM table_name
    WHERE condition);

For example, you can find the customers who are located in the same city as the supplier 'Exotic Liquid' with the following query:

SELECT CustomerName, ContactName, City
FROM Customers
WHERE City =
    (SELECT City
    FROM Suppliers
    WHERE SupplierName = 'Exotic Liquid');

This will return all the customer details who are located in the same city as 'Exotic Liquid'.

Subqueries can be a powerful tool in your SQL toolbox, allowing you to perform complex queries in a step-by-step manner, making your queries more readable and easier to debug.

In the next section, we will explore aggregation functions in SQL.

14.2 Joining Multiple Tables

In SQL, JOIN clauses are used to combine rows from two or more tables based on a related column between them. There are various types of JOINs available in SQL:

  1. (INNER) JOIN: Returns records that have matching values in both tables.
  2. LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.
  3. RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.
  4. FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table.

INNER JOIN Keyword

The INNER JOIN keyword is used to combine data from two different tables using a common column. This is particularly useful when we want to retrieve data that exists in both tables. By using the INNER JOIN keyword, we can ensure that only the records with matching values in both tables are returned.

This can help us to better understand the relationships between different pieces of data and to gain insights that we might not have been able to see otherwise. Additionally, the INNER JOIN keyword is just one of many different types of joins that we can use to combine data from multiple tables. Other types of joins include LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN, each of which has its own unique properties and use cases.

By understanding the different types of joins that are available to us, we can ensure that we are using the right tool for the job and getting the most out of our data.

Syntax:

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

Example:

Consider we have two tables, Orders and Customers, with the following structure:

Orders:

OrderID | CustomerID | OrderAmount
--------|------------|------------
1       | 1          | 100
2       | 2          | 200
3       | 5          | 300
4       | 3          | 400

Customers:

CustomerID | Name  | Country
-----------|-------|---------
1          | Alex  | USA
2          | Bob   | UK
3          | Chris | France
4          | Dave  | Canada

An INNER JOIN selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matching entries in the "Customers" table, those records will be omitted from the result.

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderAmount
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Result:

OrderID | CustomerName | OrderAmount
--------|--------------|------------
1       | Alex         | 100
2       | Bob          | 200
3       | Chris        | 400

14.2.1 LEFT JOIN and RIGHT JOIN

LEFT JOIN Keyword

The LEFT JOIN keyword is a type of join that retrieves rows from the left table (table1) and the matching rows from the right table (table2). This means that if there is no match in the right table, the resulting value will be NULL.

It is important to note that LEFT JOIN is different from INNER JOIN, as the latter only returns rows that have matching data in both tables. LEFT JOIN, on the other hand, will still show all the rows from the left table even if there is no corresponding data in the right table.

This can be useful when working with data that has missing values or when you want to see all the data from one table regardless of whether there is matching data in the other table. Additionally, LEFT JOIN can be combined with other SQL statements such as WHERE, ORDER BY, and GROUP BY to further refine the results and obtain the desired output.

Syntax:

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

Example:

SELECT Customers.CustomerName, Orders.OrderAmount
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result:

CustomerName | OrderAmount
-------------|------------
Alex         | 100
Bob          | 200
Chris        | 400
Dave         | NULL

As you can see, the LEFT JOIN keyword returns all records from the left table (Customers), and the matched records from the right table (Orders). The result is NULL on the right side, when there is no match.

RIGHT JOIN Keyword

The RIGHT JOIN keyword is used to combine data from two tables, table1 and table2. This type of join returns all the rows from table2 and the matching rows from table1. If there is no match from table1, the result will be NULL on the left side. The RIGHT JOIN is often used when you want to include all the data from table2 and only the matching data from table1.

For example, let's say you have two tables: one containing information about employees (table1) and one containing information about departments (table2). You want to display a list of all the departments, even if there are no employees in some of them. The RIGHT JOIN can be used to get all the departments from table2 and only the matching data from table1 (the employees that belong to each department).

It's worth noting that RIGHT JOIN is not a commonly used type of join. In most cases, a LEFT JOIN is used instead. However, there are some situations where a RIGHT JOIN can be useful, such as when you need to display all the data from the second table and only matching data from the first table.

Syntax:

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

Example:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderAmount
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Result:

OrderID | CustomerName | OrderAmount
--------|--------------|------------
1       | Alex         | 100
2       | Bob          | 200
4       | Chris        | 400
NULL    | Dave         | NULL

14.2.2 FULL OUTER JOIN

When using SQL to join tables, the FULL OUTER JOIN keyword can be a useful tool. This keyword returns all records in both the left (table1) and right (table2) tables, even if there is no match between them.

This means that even if a record in one table does not have a corresponding match in the other table, it will still be included in the result set. The FULL OUTER JOIN keyword is especially helpful when you want to ensure that all the data from both tables is included in the query results, regardless of whether there is a match or not.

By using this keyword, you can avoid the risk of missing important information that may be present in one table but not the other.

Syntax:

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

Example:

SELECT Customers.CustomerName, Orders.OrderAmount
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result:

CustomerName | OrderAmount
-------------|------------
Alex         | 100
Bob          | 200
Chris        | 400
Dave         | NULL
Eve          | 500

In this example, the FULL OUTER JOIN keyword returns all records when there is a match in either the left (Customers) or the right (Orders) table records. It combines the results of both left and right outer joins and returns all (matched or unmatched) records.

Please note that not all database systems support the FULL OUTER JOIN keyword. For example, MySQL does not support FULL OUTER JOIN, but you can achieve the same result by combining LEFT JOIN and UNION.

14.2.3 UNION and UNION ALL

The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within the UNION must have the same number of columns, the columns must also have similar data types, and they must also be in the same order.

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

Syntax:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

For allowing duplicate values:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Example:

SELECT city FROM Customers
UNION
SELECT city FROM Suppliers
ORDER BY city;

This SQL statement would return all distinct cities from the "Customers" and the "Suppliers" table.

Example with UNION ALL:

SELECT city FROM Customers
UNION ALL
SELECT city FROM Suppliers
ORDER BY city;

This SQL statement would return all cities (duplicate also) from the "Customers" and the "Suppliers" table.

14.2.4 Subqueries

Subqueries (also known as inner queries or nested queries) are a tool for performing operations in multiple steps. In other words, it's a query within another SQL query. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. A subquery is usually added in the WHERE Clause of the SQL Statement.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator
    (SELECT column_name(s)
    FROM table_name
    WHERE condition);

For example, you can find the customers who are located in the same city as the supplier 'Exotic Liquid' with the following query:

SELECT CustomerName, ContactName, City
FROM Customers
WHERE City =
    (SELECT City
    FROM Suppliers
    WHERE SupplierName = 'Exotic Liquid');

This will return all the customer details who are located in the same city as 'Exotic Liquid'.

Subqueries can be a powerful tool in your SQL toolbox, allowing you to perform complex queries in a step-by-step manner, making your queries more readable and easier to debug.

In the next section, we will explore aggregation functions in SQL.

Compra este libro