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
