Set Operators, Sub Query and Joins

Set Operators:

Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries

Operator	Returns
UNION	All distinct rows selected by either query
UNION ALL	All rows selected by either query, including duplicates
INTERSECT	All distinct rows selected by both queries
INTERSECT ALL	All rows selected by both queries including duplicates
MINUS	All distinct rows selected by the first query but not the second
MINUS ALL	All rows selected by the first query but not the second including duplicates
EXCEPT	All distinct rows selected by the first query but not the second
EXCEPT ALL	All rows selected by the first query but not the second including duplicates

Joins:

join is a way to combine rows from two or more tables, views, or materialized views based on a related column between them. The purpose of using joins is to retrieve data from multiple tables in a single query, allowing you to create more complex result sets.

• Inner join
• Non inner join
• Full outer join
• Left outer join
• Right outer join
• Cross join
• Self join
• Natural join
• Using clause

Sub Query

A Subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause. Subqueries can be used with the SELECT, INSERT, UPDATE and DELETE statements along with the operators like =, <, >, >=, <=, IN, etc.

Types:

• Single row
• Multiple row
• Multiple column
• Correlated
• Nested