UNIT 4

Advanced SQL:Relational SET Operators: UNION – UNION ALL – INTERSECT

MINUS.SQL Join Operators: Cross Join – Natural Join – Join USING Clause – JOIN ON
Clause – Outer Join. Sub Queries and Correlated Queries: WHERE – IN – HAVING –
ANY and ALL – FROM. SQL Functions: Date and Time Function – Numeric Function –
String Function – Conversion Function

Join Operators:

The SQL JOIN statement is used to combine rows from two tables based on a common
column and selects records that have matching values in these columns.

Syntax

The SQL JOIN syntax typically includes the JOIN clause, which specifies the tables to
combine and the related columns. The basic syntax is as follows:

SELECT column_list FROM table1 JOIN table2 ON table1.column = table2.column;

 Column_list: A list of columns to retrieve from the joined tables.

 Table1 and table2: The names of the tables to join.
 Column: The common column or key that relates the tables.

Types of joins
 Different Types of SQL JOIN

Here are the different types of the JOINs in SQL:

(INNER) JOIN: Returns records that have matching values in both tables

LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records
from the right table

RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records
from the left table

FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

(INNER) JOIN:

Returns records that have matching values in both tables

Syntax

SELECT column_name(s)

FROM table1

INNER JOIN table2

ON table1.column_name = table2.column_name;

Example

Products

ProductI ProductName
D
1 Pen
2 Pencil
 3 Scale
. Prices

ProductI Price
D
1 20
2 10

SELECT ProductName, Price

FROM Products

INNER JOIN Prices

ON Products.ProductID = Prices.ProductID;

In this example, the inner join combines the Products and Prices tables based on the
ProductID column. The query will return all records from both tables with matching values in
the ProductID column.

ProductNam Price
e
Pen 20
Pencil 10

LEFT (OUTER) JOIN:

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

Syntax
SELECT table1.column1, table2.column2

FROM table1

LEFT JOIN table2

ON table1.column1 = table2.column2;
 RIGHT (OUTER) JOIN:

Returns all records from the right table, and the matched records from the left table

Syntax

SELECT column1, column2

FROM table1

RIGHT JOIN table2

ON table1.column_name = table2.column_name;

Example
 FULL (OUTER) JOIN:

Returns all records when there is a match in either left or right table.

Syntax

SELECT *

FROM table_1

FULL JOIN table_2

ON table_1.column_name=table_2.column_name;

Example
 What Is A Cross-Join In SQL?

A cross-join in SQL is a type of join operation that combines every row from one table with
every row from another, resulting in a Cartesian product of the two tables.

Syntax

SELECT * FROM table1 CROSS JOIN table2;

If table1 has m rows and table2 has n rows, the result set will have m * n rows.

Example
 Natural Join

Natural Join in SQL combines records from two or more tables based on the common column
between them.

The common column must have the same name and data type in both the tables.

we do not need to explicitly specify the join condition.

There is no need to use the ON clause for join condition in natural join.

Syntax

SELECT * FROM

Table A NATURAL JOIN table B

Example of Natural Join in SQL

Let us create two tables employee and department and insert some data into it. We will then
implement natural join on these two tables.

Create Employee Table

CREATE TABLE employee (

EmployeeID varchar(10),

FirstName varchar(50),

DeptID varchar(10)

);

Insert Records into Employee Table

INSERT INTO employee

VALUES(“E62549”, “John”, “D1001”),

(“E82743”, “Priya”, “D3002”),

(“E58461”, “Raj”, “D1002”);

view the employee table

SELECT * FROM employee;

Output

| EmployeeID | FirstName | DeptID |

|:----------:---------:------

| E62549 | John | D1001 |

| E82743 | Priya || D3002 |

| E58461 | Raj | D1002 |

Create department table

CREATE TABLE department (

DeptID varchar(10),

DeptName varchar(40),

);

Insert records into department table

INSERT INTO department

VALUES(“D1001”, “Technology”),
 (“D1002”, “Technology”),

view the department table

SELECT * FROM department

Output

| DeptID | DeptName |

|:------:----------:

| D1001 | Technology |

| D1002 | Technology |

Natural Join on Employee and Department Tables Natural-join-in-sql code

SELECT * FROM employee NATURAL JOIN department

Output

| EmployeeID | FirstName | DeptID | DeptName |

|:----------:---------:--------:------:----------:---------

| E62549 | John | D1001 | Technology |

| E82743 | Priya | D3002 | Technology |

| E58461 | Raj | D1002 | Technology |

Difference between the USING and ON clauses in SQL:

Clause Name ON Clause USING Clause:

A Shorthand for specifying
Used to specify the join conditions when both
Usage condition for a join between tables have one or more
two tables. columns with the same
name.
JOIN table2 ON
JOIN table2 USING
Syntax table1.column =
(column_name)
table2.column
Flexibility: Allows for Simplicity: Automatically
complex conditions, handles equality checks for
Advantages
including multiple columns columns with the same name
and different comparison and eliminates duplicate
operators. columns in the result.
SELECT * FROM
SELECT * FROM
employees JOIN
employees JOIN
Example departments ON
departments USING
employees.dept_id =
(dept_id);
departments.dept_id;
Use ON for complex Use USING for simplicity
Summary conditions or different when columns share the
column names. same name.

Sub Queries And Correlated Queries

Subqueries are a powerful tool for combining data available in two tables into a single result.

An SQL subquery is nothing but a query inside another query. We use a subquery to fetch
data from two tables.

WHERE clause in a subquery

The WHERE clause in a subquery is used to filter the results of the inner query before those
results are used by the outer query. Here’s a clear example:

Example:

SELECT name

FROM employees

WHERE department_id IN (

SELECT id

FROM departments

WHERE location = ‘New York’

);

Inner Query:

SELECT id

FROM departments

WHERE location = ‘New York’;

This part retrieves the Ids of departments located in New York.

Outer Query

SELECT name

FROM employees
WHERE department_id IN (...);

This part retrieves the names of employees whose department Ids match those returned by the
inner query.

Summary:

The WHERE clause in the subquery ensures that only departments located in New York are
considered, filtering the results for the outer query.

IN clause

The IN clause in a subquery allows you to filter records based on a list of values returned by
the subquery. Here’s a straightforward example.

Example

Using the same employees and departments tables:

Table: employees

Id name department_id salary

1 Alice 1 50000
2 Bob 2 60000
3 Charlie 1 70000
4 David 3 55000

Table: departments

Id department_name
1 HR
2 IT
3 Marketing

We want to find the names of employees who work in either the “HR” or “IT” departments.
SELECT name

FROM employees

WHERE department_id IN (

SELECT id

FROM departments

WHERE department_name IN (‘HR’, ‘IT')

);

Explanation

The subquery (SELECT id FROM departments WHERE department_name IN ('HR', 'IT'))

retrieves the IDs of the HR and IT departments.

The outer query selects employee names where the department_id matches any of the IDs
returned by the subquery.

This way, it effectively filters the employees to only those who belong to either the HR or IT
departments.

HAVING clause

The HAVING clause is used to filter groups after aggregation, and it can be utilized in both
regular subqueries and correlated subqueries. Let’s go through both concepts with examples.

Example

Using the same employees and departments tables:

Table: employees

Id name department_id salary

1 Alice 1 50000
2 Bob 2 60000
3 Charlie 1 70000
 4 David 3 55000

Table: departments

Id department_name
1 HR
2 IT
3 Marketing

Let’s say we want to find departments where the average salary of employees is greater than
$55000.

SELECT department_id, AVG(salary) AS avg_salary

FROM employees

GROUP BY department_id

HAVING AVG(salary) > 55000;

Explanation

Aggregation: The query groups employees by department_id and calculates the average
salary for each department.

HAVING: The HAVING clause filters the results to only include departments with an
average salary greater than $55,000.

ANY and ALL

The ANY and ALL operators in SQL are used to compare a value against a set of values
returned by a subquery. Here’s how they work, along with examples.
 Using ANY

The ANY operator returns true if any of the subquery values meet the condition.

Example

Let’s find employees whose salary is greater than the salary of any employee in the IT
department.

SELECT name

FROM employees

WHERE salary > ANY (

SELECT salary

FROM employees

WHERE department_id = 2

);

Explanation

The subquery retrieves the salaries of all employees in the IT department.

The outer query checks if an employee’s salary is greater than any of those salaries. If at least
one condition is true, the employee is included in the result.

Using ALL

The ALL operator returns true only if all of the subquery values meet the condition.

Example

Now, let’s find employees whose salary is greater than the salary of all employees in the HR
department.
SELECT name

FROM employees

WHERE salary > ALL (

SELECT salary

FROM employees

WHERE department_id = 1

);

Explanation

The subquery retrieves the salaries of all employees in the HR department.

The outer query checks if an employee’s salary is greater than all of those salaries. If true for
every salary returned by the subquery, the employee is included in the result.

Summary

ANY: Returns true if at least one of the subquery values satisfies the condition.

ALL: Returns true only if all subquery values satisfy the condition.

Both operators allow you to compare a single value against a set of values from a subquery,
enabling complex filtering conditions in your queries.

SQL functions

SQL functions are built-in programs that a relational database application uses to manipulate
data in some way.

A function in SQL is a set of SQL statements that perform a specific task on data or database
objects:

Definition

A function is a reusable code block that’s created and stored in the database.

How it works

A function accepts input parameters, performs actions, and returns a result.

 When to use it

Functions are useful for code reusability, especially when you need to repeatedly write the
same SQL scripts.

How to call it

A function can be called from an SQL statement or another function.

Types

There are many types of functions, including data definition language (DDL), data
manipulation language (DML), and data query language (DQL) functions.

SQL Server comes with built-in functions, but you can also create your own user-defined
functions.