Wednesday, August 7, 2024

SQL Set Operations

The SQL Set operations is used to combine the two or more sql statements.

Types of Set operations:

  • Union
  • Unionall
  • Intersect
  • Minus
Union Operation:
  • 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 columns in every SELECT statement must also be in the same order
  • The union operation eliminates the duplicate rows from its resultset.
 Syntax

 SELECT Column_name FROM table1 
UNION
 SELECT column_name FROM table2;

 Example
 SELECT * FROM First 
UNION
 SELECT * FROM Second;

Union all operation:

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Intersect Operation

  • 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
SELECT * FROM First
INTERSECT
SELECT * FROM Second;

MINUSOperation

  • 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
SELECT * FROM First
MINUS
SELECT * FROM Second

No comments:

Post a Comment

Featured Post

End to End Securing Data in Oracle Integration Cloud (OIC) with OCI Vault: AES for Encryption & Decryption & RSA for Signing and verification | Implement message-level encryption in Oracle Integration using OCI Vault

Scenario: A company is integrating Oracle Integration Cloud (OIC) with an external financial system to exchange sensitive payroll data secur...