Sign inGet started

Advanced SQL Queries

INF2603 - Databases I · Structured Query Language (SQL)

Advanced SQL Queries

Advanced SQL queries allow you to perform complex data retrieval and manipulation tasks in a relational database. In this section, you will learn about subqueries, joins, and set operations.

Subqueries

A subquery is a query nested within another SQL query. It can return individual values or a set of records. Subqueries are often used in the WHERE clause to filter results based on another query's results.

Example of a Subquery

Consider a database with two tables: Employees and Departments. The Employees table has the following fields:

  • EmployeeID
  • Name
  • DepartmentID

The Departments table has these fields:

  • DepartmentID
  • DepartmentName

To find the names of employees working in a specific department, you can use a subquery. For example, to find all employees in the 'Sales' department, you can write:

SELECT Name FROM Employees WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales');

This query first retrieves the DepartmentID for 'Sales' and then finds the names of employees in that department.

Watch out: Ensure that the subquery returns a single value when using it in the WHERE clause. If it returns multiple values, you should use the IN operator instead.

Joins

Joins are used to combine rows from two or more tables based on a related column between them. There are several types of joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

INNER JOIN

An INNER JOIN returns records that have matching values in both tables. For example, to retrieve employee names along with their department names, you can use the following query:

SELECT Employees.Name, Departments.DepartmentName FROM Employees INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

This query returns a list of employee names and their corresponding department names where there is a match in DepartmentID.

LEFT JOIN

A LEFT JOIN returns all records from the left table and the matched records from the right table. If there is no match, NULL values are returned for columns from the right table. For example:

SELECT Employees.Name, Departments.DepartmentName FROM Employees LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

This query will return all employees, including those who may not belong to any department, with NULL in the DepartmentName column for those employees.

RIGHT JOIN

A RIGHT JOIN is the opposite of a LEFT JOIN. It returns all records from the right table and the matched records from the left table. If there is no match, NULL values are returned for columns from the left table. For example:

SELECT Employees.Name, Departments.DepartmentName FROM Employees RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

This query will return all departments, including those without employees, with NULL in the Name column for those departments.

FULL OUTER JOIN

A FULL OUTER JOIN returns all records when there is a match in either left or right table records. For example:

SELECT Employees.Name, Departments.DepartmentName FROM Employees FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

This query will return all employees and all departments, with NULLs in the respective columns where there are no matches.

Watch out: Be careful when using joins with large tables, as they can significantly increase the amount of data processed and affect performance.

Set Operations

Set operations allow you to combine the results of two or more SELECT statements. The main set operations are UNION, UNION ALL, INTERSECT, and EXCEPT.

UNION

The UNION operator combines the results of two SELECT statements and removes duplicate rows. For example, if you want to retrieve a list of all unique department names from two different tables:

SELECT DepartmentName FROM Departments1 UNION SELECT DepartmentName FROM Departments2;

This query will return a list of unique department names from both tables.

UNION ALL

The UNION ALL operator combines the results of two SELECT statements but includes all duplicates. For example:

SELECT DepartmentName FROM Departments1 UNION ALL SELECT DepartmentName FROM Departments2;

This query will return all department names from both tables, including duplicates.

INTERSECT

The INTERSECT operator returns only the rows that are common to both SELECT statements. For example:

SELECT DepartmentName FROM Departments1 INTERSECT SELECT DepartmentName FROM Departments2;

This query will return department names that exist in both tables.

EXCEPT

The EXCEPT operator returns rows from the first SELECT statement that are not present in the second SELECT statement. For example:

SELECT DepartmentName FROM Departments1 EXCEPT SELECT DepartmentName FROM Departments2;

This query will return department names that are in Departments1 but not in Departments2.

Remember: When using set operations, the number of columns and their data types must be the same in all SELECT statements.

Summary

  • Subqueries are nested queries used to filter results.
  • Joins combine rows from multiple tables based on related columns.
  • Set operations combine results from multiple SELECT statements.

Check your understanding

  1. What is the purpose of a subquery?
  2. Explain the difference between INNER JOIN and LEFT JOIN.
  3. What does the UNION ALL operator do?
  4. How does the EXCEPT operator differ from the INTERSECT operator?