What is UNION in SQL?
The UNION operator in SQL is used to combine the result sets of two or more SELECT queries. It combines the
results and removes duplicate rows by default. If you want to include all rows (including duplicates), you can use
UNION ALL .
Key Points about UNION:
Each SELECT statement within the UNION must have the same number of columns .
The corresponding columns must have similar data types .
The columns in each SELECT statement must be in the same order.
Syntax :
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
The number of columns and their data types in both SELECT queries must match for the UNION to work.
Example 1: Basic UNION
Let's say we have two tables: employees_in_2023 and employees_in_2024. We want to combine the list of
employee names from both years.
Table: employees_in_2023
employee_id name
1 Alice Johnson
2 Bob Smith
3 Charlie Davis
Table: employees_in_2024
employee_id name
2 Bob Smith
4 David Lee
5 Eva Martinez
-- Create the employees_in_2023 table
CREATE TABLE employees_in_2023 (
employee_id INT PRIMARY KEY,
name VARCHAR(100)
);
INSERT INTO employees_in_2023 (employee_id, name) VALUES (1, 'Alice Johnson');
INSERT INTO employees_in_2023 (employee_id, name) VALUES (2, 'Bob Smith');
INSERT INTO employees_in_2023 (employee_id, name) VALUES (3, 'Charlie Davis');
-- Create the employees_in_2024 table
CREATE TABLE employees_in_2024 (
employee_id INT PRIMARY KEY,
name VARCHAR(100)
);
INSERT INTO employees_in_2024 (employee_id, name) VALUES (2, 'Bob Smith');
INSERT INTO employees_in_2024 (employee_id, name) VALUES (4, 'David Lee');
INSERT INTO employees_in_2024 (employee_id, name) VALUES (5, 'Eva Martinez');
SQL Query with UNION:
SELECT name
FROM employees_in_2023
UNION
SELECT name
FROM employees_in_2024;
Result :
name
Alice Johnson
Bob Smith
Charlie Davis
David Lee
Eva Martinez
Explanation:
The query combines the names from both tables but removes duplicates (like "Bob Smith") by default.
Example 2: UNION ALL
If you want to include duplicate rows, use UNION ALL :
SELECT name
FROM employees_in_2023
UNION ALL
SELECT name
FROM employees_in_2024;
Result with UNION ALL:
name
Alice Johnson
Bob Smith
Charlie Davis
Bob Smith
David Lee
Eva Martinez
Eva Martinez
name
Explanation:
UNION ALL includes duplicates, so "Bob Smith" appears twice in the result.
Example 3: UNION with Different WHERE Clauses
You can apply conditions in each SELECT query independently. For example, to combine names of employees
earning above a certain salary from two different tables:
SELECT name
FROM employees_in_2023
WHERE salary > 50000
UNION
SELECT name
FROM employees_in_2024
WHERE salary > 60000;
Important Notes:
1. The number of columns must match across both queries.
2. The data types of the corresponding columns should be compatible (e.g., INT with INT , VARCHAR with
VARCHAR ).
3. If you use UNION ALL , duplicate rows will not be removed.
What is a Subquery in SQL?
A subquery (also known as an inner query or nested query) is a query within another SQL query. It is typically
enclosed within parentheses and can be used to return data that will be used by the main query. Subqueries can
be used in various places such as SELECT , FROM , WHERE , or even in INSERT , UPDATE , or DELETE
statements.
Types of Subqueries:
1. Single-row subqueries : Returns one row.
2. Multi-row subqueries : Returns multiple rows.
3. Correlated subqueries : Refers to a column in the outer query, and thus, it is re-executed for each row of the
outer query.
4. Non-correlated subqueries : Independent from the outer query and can be executed separately.
Syntax of a Subquery:
SELECT column1, column2, ...
FROM table1
WHERE column1 = (SELECT column FROM table2 WHERE condition);
Example 1: Subquery in the WHERE Clause
Suppose we have two tables: employees and departments.
employees Table:
employee_id name department_id salary
1 Alice Johnson 1 60000
2 Bob Smith 2 70000
3 Charlie Davis 1 55000
4 David Lee 3 65000
5 Eva Martinez NULL 50000
departments Table :
department_id department_name
1 HR
2 IT
3 Marketing
Query: Find the employees who belong to the "IT" department.
SELECT name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name
= 'IT');
Explanation:
The subquery retrieves the department_id for the "IT" department.
The main query then uses this department_id to find all employees belonging to that department.
Example 2: Subquery in the SELECT Clause
This subquery can be used to fetch some aggregated value.
Query: List each employee along with the average salary of all employees.
SELECT name, salary,
(SELECT AVG(salary) FROM employees) AS average_salary
FROM employees;
Explanation:
The subquery (SELECT AVG(salary) FROM employees) calculates the average salary of all employees.
The main query lists the name , salary , and the average_salary for each employee.
Example 3: Subquery in the FROM Clause
You can use a subquery in the FROM clause by treating it as a temporary table.
Query: Find the maximum salary in each department.
SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;
Now, using a subquery to get department names for better readability:
SELECT departments.department_name, temp.max_salary
FROM departments
JOIN (SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id) AS temp
ON departments.department_id = temp.department_id;
Explanation:
The subquery (SELECT department_id, MAX(salary) ... ) calculates the maximum salary for each
department.
This result is treated as a temporary table ( temp ), which is joined with the departments table to display
department names.
Example 4: Correlated Subquery
A correlated subquery refers to a column in the outer query and is executed for each row of the outer query.
Query: Find employees whose salary is above the average salary in their department.
SELECT name, salary
FROM employees e
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id);
Explanation:
The subquery calculates the average salary for the department of the current employee
( e.department_id ).
The main query then selects employees whose salary is greater than the department average.
Practice Questions:
1. Write a query to find the names of employees who earn more than the average salary.
2. Write a query to list all employees who do not belong to the "Marketing" department.
3. Write a query to find departments that have no employees.
4. Write a query to get the names of employees along with the number of employees in their department.
What is a View in MySQL?
A view in MySQL is a virtual table that is based on the result set of a stored query. It can contain rows and
columns just like a real table, but it does not store the data itself. Instead, a view is defined by a SQL query that
retrieves data from one or more tables.
Benefits of Using Views:
1. Simplification: Views can simplify complex queries by encapsulating them. Users can interact with the view
as if it were a regular table.
2. Security: Views can restrict access to certain columns or rows in a table, allowing users to see only the data
they are authorized to view.
3. Data Abstraction : Changes to the underlying tables do not affect how users interact with the view.
4. Reusability: A view can be reused in multiple queries, promoting code reuse.
Creating a View:
The basic syntax for creating a view is:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example 1: Creating a Simple View
Suppose you have the following employees table:
employees Table:
employee_id name department_id salary
1 Alice Johnson 1 60000
2 Bob Smith 2 70000
3 Charlie Davis 1 55000
4 David Lee 3 65000
5 Eva Martinez NULL 50000
You want to create a view that shows only employees with a salary greater than 60000.
SQL to Create the View:
CREATE VIEW high_salary_employees AS
SELECT name, salary
FROM employees
WHERE salary > 60000;
Querying the View:
You can query the view just like a regular table:
SELECT * FROM high_salary_employees;
Example Result:
name salary
Bob Smith 70000
David Lee 65000
Updating a View:
In some cases, you can update the data in the underlying tables through a view. This is called an "updatable
view."
Example 2: Updating the View:
If you want to update an employee's salary through the view:
If you want to update an employee's salary through the view:
UPDATE high_salary_employees
SET salary = 75000
WHERE name = 'Bob Smith';
Refreshing a View:
When the underlying data changes, the view reflects these changes automatically when queried.
Dropping a View:
If you no longer need the view, you can remove it:
DROP VIEW high_salary_employees;
Example 3: Joining Tables in a View
You can create a view that combines data from multiple tables. For example, if you have a departments table:
departments Table :
department_id department_name
1 HR
2 IT
3 Marketing
Creating a View with Join:
CREATE VIEW employee_department AS
SELECT [Link] AS employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
Querying the View:
SELECT * FROM employee_department;
Example Result:
employee_name department_name
Alice Johnson HR
Bob Smith IT
Charlie Davis HR
David Lee Marketing
Practice Questions:
1. Create a view that lists all employees and their corresponding department names.
2. Create a view that shows only employees with a salary less than 55000.
3. Create a view that aggregates the total salary for each department.
4. Drop a view and explain the implications.
BY Pranjal Gajbhiye(AIE)
BY Pranjal Gajbhiye(AIE)
Happy Learning....
In [ ]: