UNIONS CLAUSE:
·
The SQL UNION
clause/operator is used to combine the results of two or more SELECT statements
without returning any duplicate rows.
·
To use UNION, each SELECT must have the same number of
columns selected, the same number of column expressions, the same data type,
and have them in the same order, but they do not have to be the same length.
Syntax:
SELECT
column1 [, column2 ]
FROM
table1 [, table2 ]
[WHERE
condition]
UNION
SELECT
column1 [, column2 ]
FROM
table1 [, table2 ]
[WHERE condition]
EXAMPLE: CUSTOMER TABLE
Table: orders
SELECT
c.customerid, firstname, totalamount, orderdate
FROM customer c
LEFT JOIN orders o
ON c.customerid = o.customerid
UNION
SELECT c.customerid, firstname,
totalamount, orderdate
FROM customer c
RIGHT JOIN orders o
ON c.customerid = o.customerid
UNION ALL Clause:
The UNION ALL operator is used to combine the results of
two SELECT statements including duplicate rows.
Syntax:
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
Example:
Consider the following two tables, (a) CUSTOMERS table
and ORDER table.
SELECT
c.customerid, firstname, totalamount, orderdate
FROM customer c
LEFT JOIN orders o
ON c.customerid = o.customerid
UNION
ALL
SELECT c.customerid, firstname,
totalamount, orderdate
FROM customer c
RIGHT JOIN orders o
ON c.customerid = o.customerid
Two types of Union clauses:
1. INTERSECT
Clause:
The SQL INTERSECT clause/operator is used to
combine two SELECT statements, but returns rows only from the first SELECT
statement that are identical to a row in the second SELECT statement. This
means INTERSECT returns only common rows returned by the two SELECT statements.
Syntax:
SELECT
column1 [, column2 ]
FROM
table1 [, table2 ]
[WHERE
condition]
INTERSECT
SELECT
column1 [, column2 ]
FROM
table1 [, table2 ]
[WHERE condition]
Here given condition could be any given expression based
on your requirement.
Example:
SELECT
c.customerid, firstname, totalamount, orderdate
FROM customer c
LEFT JOIN orders o
ON c.customerid = o.customerid
INTERSECT
SELECT c.customerid, firstname,
totalamount, orderdate
FROM customer c
RIGHT JOIN orders o
ON c.customerid = o.customerid
2. EXCEPT Clause:
The SQL EXCEPT
clause/operator is used to combine two SELECT statements and returns rows from
the first SELECT statement that are not returned by the second SELECT
statement. This means EXCEPT returns only rows, which are not available in
second SELECT statement.
Syntax:
SELECT
column1 [, column2 ]
FROM
table1 [, table2 ]
[WHERE
condition]
EXCEPT
SELECT
column1 [, column2 ]
FROM
table1 [, table2 ]
[WHERE condition]
Here given condition could be any given expression based
on your requirement.
Example:
Consider the following two tables, CUSTOMERS table and ORDER table is as
follows:
SELECT
c.customerid, firstname, totalamount, orderdate
FROM customer c
LEFT JOIN orders o
ON c.customerid = o.customerid
EXCEPT
SELECT c.customerid, firstname,
totalamount, orderdate
FROM customer c
RIGHT JOIN orders o
ON c.customerid = o.customerid
No comments:
Post a Comment