PostgreSQL union | PostgreSQL union all | PostgreSQL intersect
Table of Contents
PostgreSQL: using Set Operators: Postgresql UNION
Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries. set operators include PostgreSQL union, PostgreSQL intersect etc.,
There are four types of set operators, they are
Operator Returns
UNION Distinct rows selected by either query
UNION ALL All rows selected by either query, including all duplicates
INTERSECT All distinct rows selected by both queries
EXCEPT All distinct rows selected by the first query but not the second
Lets understand the above set operators using examples.
PostgreSQL UNION Clause
Syntax
select statement_1 union select statement_2;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
testdb=> select * from emp where sal > 1000 UNION select * from emp where sal < 2000; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-----------+-----------+------+----------------------------------+---------+---------+-------- 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00+05:30 | 1250.00 | 500.00 | 30 7777 | ADAMSMITH | CLERK | 5555 | 2018-12-30 09:22:56.546907+05:30 | 666.00 | | 20 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00+05:30 | 1500.00 | 4.00 | 30 7839 | KING | PRESIDENT | 4554 | 1981-11-17 00:00:00+05:30 | 5000.00 | 68.00 | 10 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00+05:30 | 1250.00 | 1400.00 | 30 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00+05:30 | 800.00 | 300.00 | 20 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00+05:30 | 3000.00 | 67.00 | 20 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00+05:30 | 950.00 | 11.00 | 30 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00+05:30 | 2850.00 | 20.00 | 30 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00+05:30 | 1100.00 | 4.00 | 20 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00+05:30 | 2975.00 | 300.00 | 20 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00+05:30 | 1300.00 | 16.00 | 10 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00+05:30 | 1600.00 | 300.00 | 30 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00+05:30 | 3000.00 | 1.00 | 20 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00+05:30 | 2450.00 | 10.00 | 10 (15 rows) |
PostgreSQL UNION ALL Clause
Syntax
select statement_1 union all select statement_2;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
testdb=> select * from emp where sal > 1000 UNION ALL select * from emp where sal < 2000; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-----------+-----------+------+----------------------------------+---------+---------+-------- 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00+05:30 | 1600.00 | 300.00 | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00+05:30 | 1250.00 | 500.00 | 30 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00+05:30 | 2975.00 | 300.00 | 20 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00+05:30 | 1250.00 | 1400.00 | 30 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00+05:30 | 2850.00 | 20.00 | 30 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00+05:30 | 2450.00 | 10.00 | 10 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00+05:30 | 3000.00 | 67.00 | 20 7839 | KING | PRESIDENT | 4554 | 1981-11-17 00:00:00+05:30 | 5000.00 | 68.00 | 10 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00+05:30 | 1500.00 | 4.00 | 30 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00+05:30 | 1100.00 | 4.00 | 20 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00+05:30 | 3000.00 | 1.00 | 20 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00+05:30 | 1300.00 | 16.00 | 10 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00+05:30 | 800.00 | 300.00 | 20 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00+05:30 | 1600.00 | 300.00 | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00+05:30 | 1250.00 | 500.00 | 30 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00+05:30 | 1250.00 | 1400.00 | 30 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00+05:30 | 1500.00 | 4.00 | 30 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00+05:30 | 1100.00 | 4.00 | 20 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00+05:30 | 950.00 | 11.00 | 30 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00+05:30 | 1300.00 | 16.00 | 10 7777 | ADAMSMITH | CLERK | 5555 | 2018-12-30 09:22:56.546907+05:30 | 666.00 | | 20 (21 rows) |
PostgreSQL INTERSECT Clause
Syntax
select statement_1 intersect select statement_2;
|
1 2 3 4 5 6 7 8 9 10 |
testdb=> select * from emp where sal > 1000 INTERSECT select * from emp where sal < 2000; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+----------+------+---------------------------+---------+---------+-------- 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00+05:30 | 1250.00 | 500.00 | 30 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00+05:30 | 1100.00 | 4.00 | 20 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00+05:30 | 1500.00 | 4.00 | 30 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00+05:30 | 1250.00 | 1400.00 | 30 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00+05:30 | 1300.00 | 16.00 | 10 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00+05:30 | 1600.00 | 300.00 | 30 (6 rows) |
PostgreSQL EXCEPTClause
Syntax
select statement_1 exceptselect statement_2;
|
1 2 3 4 5 6 7 8 9 10 11 12 |
testdb=> select * from emp where sal > 1000 EXCEPT select * from emp where sal < 2000; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-------+-----------+------+---------------------------+---------+--------+-------- 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00+05:30 | 2850.00 | 20.00 | 30 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00+05:30 | 2975.00 | 300.00 | 20 7839 | KING | PRESIDENT | 4554 | 1981-11-17 00:00:00+05:30 | 5000.00 | 68.00 | 10 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00+05:30 | 3000.00 | 67.00 | 20 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00+05:30 | 3000.00 | 1.00 | 20 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00+05:30 | 2450.00 | 10.00 | 10 (6 rows) testdb=> |
Words from postgreshelp
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.
