SQL SET and JOIN
Operations
SQL SET Operation
SQL SET Operation
The SQL Set operation is used to combine the two or more SQL
SELECT statements.
Types of Set Operation
Union
UnionAll
Intersect
Minus
Union
The SQL Union operation is used to combine the result of two or more
SQL SELECT queries.
In the union operation, all the number of datatype and columns must
be same in both the tables on which UNION operation is being applied.
The union operation eliminates the duplicate rows from its resultset.
Syntax
SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;
Union
Example:
SELECT * FROM First UNION SELECT * FROM Second;
Union All
Union All operation is equal to the Union operation. It returns the set without
removing duplication and sorting the data.
Syntax
SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2;
Example: Using the above First and Second table.
SELECT * FROM First
UNION ALL
SELECT * FROM Second;
Union All
Intersect
It is used to combine two SELECT statements. The Intersect operation
returns the common rows from both the SELECT statements.
In the Intersect operation, the number of datatype and columns must be
the same.
It has no duplicates and it arranges the data in ascending order by
default.
Syntax
SELECT column_name FROM table1
INTERSECT
SELECT column_name FROM table2;
Example: Using the above First and Second table.
SELECT * FROM First
INTERSECT
SELECT * FROM Second;
Intersect
Minus/Set Differende
It combines the result of two SELECT statements. Minus operator is
used to display the rows which are present in the first query but absent
in the second query.
It has no duplicates and data arranged in ascending order by default
Syntax
SELECT column_name FROM table1
MINUS
SELECT column_name FROM table2;
Example: Using the above First and Second table.
SELECT * FROM First
MINUS
SELECT * FROM Second;
Minus/Set Differende
SQL JOIN Operation
SQL Join Operation
In SQL, joins are used to combine records from two or more tables based on a
related column between them. They help to bring data together, making it
possible to query data from multiple tables in a relational database.
There are different types of joins that are commonly used, each with its own
purpose and behavior. These include:
▪ INNER JOIN
▪ OUTER JOIN
▪ LEFT JOIN
▪ RIGHT JOIN
SQL Join Operation
Inner Join
The INNER JOIN returns only those records that have matching values in both tables.
It excludes rows that do not have a match in one of the tables.
Usage: Use INNER JOIN when you want to select only the rows that have corresponding
matches in both tables.
Syntax:
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.common_column =
table2.common_column;
Performance Consideration: INNER JOIN can be efficient when tables have a strong
relationship through foreign keys. However, it may perform poorly if the dataset is large
and the join condition is complex.
Inner Join
Example:
SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;
Outer Join
An OUTER JOIN returns all records when there is a match in either left or right table
records. There are two types of OUTER JOIN:
LEFT JOIN (or LEFT OUTER JOIN)
RIGHT JOIN (or RIGHT OUTER JOIN)
Usage: Use OUTER JOIN when you want to retain all rows from one or both tables, even
if there is no match between them.
Left Outer Join
The LEFT JOIN returns all records from the left table and the matched records from
the right table. If there is no match, the result is NULL on the side of the right table.
Syntax:
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;
Example: Retrieve all customers and their orders, including customers who have not
placed any orders.
SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
Right Outer Join
The RIGHT JOIN returns all records from the right table and the matched records
from the left table. If there is no match, the result is NULL on the side of the left
table.
Syntax:
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;
Example: Retrieve all orders and the customers who placed them, including orders that
do not have associated customer details.
SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
Full Outer Join
The FULL OUTER JOIN returns all records when there is a match in either left or right
table records. This join combines the result of LEFT JOIN and RIGHT JOIN, returning rows
that have matches in both tables and rows with unmatched data in either table.
Syntax:
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;
Example: Get all customers and all orders, including those that do not have a corresponding
match in the other table.
SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;
Example Tables
Example Tables
create table Department create table Manager
(
(
Manager_ID varchar(10) primary key,
Dept_ID varchar(10) primary key, Manager_Name varchar(10) NOT NUll,
Dep_Name varchar(10) NOT NULL Department_ID varchar(10),
foreign key(Department_ID) references
) Department(Dept_ID)
insert into Department values )
('D1','IS'),
insert into Manager values
('D2','CS'),
('M1','Dawit','D1'),
('D3','IT'), ('M2','Hiwot','D2'),
('D4','SE'), ('M3','Abdisa','D3')
('D5','EE')
Example Tables
create table Employee create table Project
(
Emp_ID varchar(10) primary key, (
Emp_Name varchar(10) NOT NULL, Proj_ID varchar(10) primary key,
salary float NOT NULL, Proj_Name varchar(20) NOT NULL,
Dept_ID varchar(10), Team_Memb_ID varchar(10),
Manager_ID varchar(10), foreign key(Team_Memb_ID) references
foreign key(Dept_ID) references Employee(Emp_ID)
Department(Dept_ID),
foreign key(Manager_ID) references )
Manager(Manager_ID)
insert into Project values
)
insert into Employee values ('P1','DB','E1'),
('E1','Dawit',25000,'D1','M1'), ('P2','Networking','E3'),
('E2','Meklit',15000,'D1','M1'),
('P3','HW','E4'),
('E3','Hiwot',25000,'D2','M2'),
('E4','Mesay',20000,'D2','M2'), ('P4','HNS','E2')
('E5','Abdisa',25000,'D3','M3'),
('E6','Siraj',20000,'D1','M1'),
('E7','Nahom',18000,'D2','M2'),
('E8','Yoseph',14000,'D3','M3')
Example Tables
--fetch the Employee Name along with the Department Name they belong to.
--join/inner join
select Emp_Name, Dep_Name from Employee
inner join Department on Employee.Dept_ID = Department.Dept_ID
--or
select e.Emp_Name, d.Dep_Name from Employee e
inner join Department d on e.Dept_ID =d.Dept_ID
Example Tables
-- fetch All the Employee Name and their Department Name they belong to.
-- Left join
select e.Emp_Name, d.Dep_Name from Employee e
left join Department d on e.Dept_ID =d.Dept_ID
Example Tables
fetch All the Department Name and their Employee Name they belong to.
select d.Dep_Name, e.Emp_Name from Department d
left join Employee e on d.Dept_ID = e.Dept_ID
Full Outer Join
-- fetch All the Employee Name and their Department Name they belong to.
-- Right join
select e.Emp_Name, d.Dep_Name from Employee e
Right join Department d on e.Dept_ID =d.Dept_ID
-- right join = inner join + any additional records in the right table.
Full Outer Join
--Fetch details of all employee, their manager, their department and the projects they
works on.
select e.Emp_Name, d.Dep_Name, m.Manager_Name from
Employee e left join Department d on e.Dept_ID =d.Dept_ID
inner join Manager m on m.Manager_ID = e.Manager_ID
Full Outer Join
--Fetch details of all employee, their manager, their department and the projects they
works on.
select e.Emp_Name, d.Dep_Name, m.Manager_Name, p.Proj_Name from
Employee e right join Department d on e.Dept_ID =d.Dept_ID
inner join Manager m on m.Manager_ID = e.Manager_ID
join Project p on p.Team_Memb_ID = e.Emp_ID
Example Tables
select e.Emp_Name, d.Dep_Name, m.Manager_Name, p.Proj_Name from
Employee e right join Department d on e.Dept_ID =d.Dept_ID
inner join Manager m on m.Manager_ID = e.Manager_ID
left join Project p on p.Team_Memb_ID = e.Emp_ID
Example Tables
select e.Emp_Name, d.Dep_Name from Employee e
FULL OUTER JOIN Department d on e.Dept_ID =d.Dept_ID
