Aliases, Subqueries, and Relationships

---

1. Using aliases for columns and tables

What are aliases in SQL?

In SQL, aliases are temporary names assigned to tables or columns within a query. They are especially useful for improving the readability of complex queries or creating more descriptive and easy-to-understand names.

An alias only exists during the execution of the query and does not affect the original database.

Why use aliases?

Using aliases allows you to:

• Simplify code: Especially in complex queries where multiple tables or subqueries are used.
• Improve readability: You can give more understandable names to columns or tables.
• Avoid ambiguity: In queries with multiple tables that contain columns with the same name, aliases help differentiate those columns.

How are aliases used?

Column Aliases

Column aliases allow you to give more meaningful or abbreviated names to column results. They are assigned using the AS keyword.

Example:

SELECT first_name AS name, last_name AS surname
FROM employees;

In this example, first_name and last_name are the original columns of the employees table, but in the results they will be shown as name and surname, respectively.

If you prefer, you can omit the word AS and write the alias directly:

SELECT first_name name, last_name surname
FROM employees;

Table Aliases

Table aliases are useful when working with multiple tables and we want to make the code cleaner and easier to read. Table aliases are especially common when using joins between multiple tables.

Example:

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

In this case:

• The employees table has been assigned the alias e.
• The departments table has been assigned the alias d.

This makes column references shorter and clearer, avoiding having to write long table names repeatedly.

Remember that aliases do not have to be unique, but they must be consistent within a query.

Aliases in subqueries

Aliases are also useful when working with subqueries (queries within other queries). In this case, the alias makes it easier to refer to the subquery results.

Example:

SELECT e.first_name, e.last_name
FROM employees e
WHERE e.department_id IN (SELECT department_id FROM departments WHERE department_name = 'IT');

In this example, the subquery gets the department_id from the departments table with the alias d, and then that result is used in the main query to get the employees who belong to that department.

Best practices when using aliases

• Clarity: Use descriptive aliases.
• Consistency: Be consistent with their use.
• Avoid conflicts: Do not use reserved words.

Alias Activity

1. Objective: Create a query that uses aliases for both columns and tables. Use a subquery to filter results.
2. Requirements:
   • Use aliases for the customers, orders, and products tables.
   • Select the customer name, order total, and product name.
   • Filter results for products exceeding a certain price.

SELECT c.customer_name, o.order_total, p.product_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE p.price > 50;

---

2. Subqueries in WHERE and FROM

What is an SQL subquery?

A subquery (or nested query) is a SELECT statement that is contained within another SQL query. It is used to get intermediate results or dynamic criteria and can appear in different parts of a query, such as WHERE, FROM, or SELECT.

Features:

• Evaluates before the main query.
• Can return a value, a list, or a temporary table.
• Improves modularity and readability of SQL code.

What is a subquery used for?

• Filter records using data from another table.
• Perform aggregated calculations without several separate queries.
• Avoid complex joins, when they are not required.
• Build temporary tables for advanced operations.
• Compare values dynamically.

Types of subqueries according to their location

Subquery in WHERE

Filters records based on the result of another query.

SELECT name
FROM employees
WHERE department_id IN (
  SELECT id
  FROM departments
  WHERE region = 'North'
);

Filters employees whose department_id belongs to departments in the 'North' region.

Subquery in FROM

Acts as a temporary table within the main query.

SELECT d.region, COUNT(e.id) AS total_employees
FROM (
  SELECT id, region
  FROM departments
  WHERE region IN ('North', 'South')
) AS d
JOIN employees e ON e.department_id = d.id
GROUP BY d.region;

Generates employee stats by region, using a derived intermediate table.

Quick comparison

Location	Main use	Advantage
WHERE	Filter data	Simple, direct syntax
FROM	Create intermediate sets	Allows grouping, joining, analyzing

Combined example (both ways)

SELECT e.name, e.position
FROM employees e
WHERE e.department_id IN (
  SELECT d.id
  FROM (
    SELECT id, region
    FROM departments
    WHERE region = 'North'
  ) AS d
  JOIN employees e2 ON e2.department_id = d.id
  GROUP BY d.id
  HAVING COUNT(e2.id) > 5
);

Here a subquery is used in FROM to isolate departments in the 'North' region and then filters in WHERE only those with more than 5 employees.

Best practices

• Use EXISTS instead of IN if you expect many results.
• Avoid correlated subqueries on large volumes.
• Index the columns used in subqueries.
• Consider using CTEs (WITH) for greater clarity in complex queries.

---

3. Relating tables using (JOIN, INNER, LEFT)

What is a JOIN?

A JOIN is an SQL statement that allows combining data from two or more tables into a single result set. It does this by joining rows that have a common value in one or more columns.

In other words:

A JOIN relates tables through an equality condition between columns (usually a primary key in one table and a foreign key in the other), and merges columns from both tables in the final result.

JOIN does not create a new table, it only temporarily combines the results to show unified information.

Why use JOIN?

• To get related information from multiple tables in a single query
• Reduces data duplication and improves normalization
• Facilitates complex reports with multiple entities
• Optimizes queries in well-structured databases

Common uses of JOIN

• Link customers with their orders
• Relate students with enrolled courses
• Combine products with categories
• Display users along with their profiles or roles

Most common types of JOIN

All examples are based on two tables: Customers and Orders, joined by the customer_id column.

Customers Table

customer_id	name
1	Juan
2	Maria
3	Pedro

Orders Table

order_id	customer_id	product
101	1	Keyboard
102	2	Mouse
103	1	Monitor

INNER JOIN

Returns only the records that match in both tables.

SELECT Customers.name, Orders.product
FROM Customers
INNER JOIN Orders
ON Customers.customer_id = Orders.customer_id;

Result:

name	product
Juan	Keyboard
Maria	Mouse
Juan	Monitor

Pedro does not appear because he has no orders.

LEFT JOIN

Returns all records from the left table and the matched ones from the right. If there is no match, it is filled with NULL.

SELECT Customers.name, Orders.product
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer_id;

Result:

name	product
Juan	Keyboard
Juan	Monitor
Maria	Mouse
Pedro	NULL

All customers are kept, even those without orders.

RIGHT JOIN

Returns all records from the right table and the matched ones from the left.

SELECT Customers.name, Orders.product
FROM Customers
RIGHT JOIN Orders
ON Customers.customer_id = Orders.customer_id;

Result:

name	product
Juan	Keyboard
Maria	Mouse
Juan	Monitor

There is no difference if all orders have a customer.

FULL JOIN

Returns all records when there is a match in one of the tables or both. If there is no match, they are filled with NULL.

SELECT Customers.name, Orders.product
FROM Customers
FULL JOIN Orders
ON Customers.customer_id = Orders.customer_id;

Result:

name	product
Juan	Keyboard
Maria	Mouse
Juan	Monitor
Pedro	NULL

All information is displayed, even when there is no match in any table.

Practical example of INNER JOIN

Relate employees with their department:

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

Proposed activity

Perform a LEFT JOIN query that relates a users table with comments.
It must show all users, even if they have not made comments.

SELECT users.name, comments.text
FROM users
LEFT JOIN comments
ON users.id = comments.user_id;