YouTip

# PostgreSQL UNION Operator The PostgreSQL UNION operator combines the result sets of two or more SELECT statements. The UNION operator is used to combine the result sets of two or more SELECT statements. Please note that each SELECT statement within UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order. ### Syntax The basic syntax of UNION is as follows: SELECT column1 [, column2 ] FROM table1 [, table2 ] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] The WHERE condition here can be set to any expression as per your requirement. ### Example Create the COMPANY table ((https://static.jyshare.com/download/company.sql) ), with the following data content: tutorialdb# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000(7 rows) Create the DEPARTMENT table ((https://static.jyshare.com/download/department.sql) ), with the following data content: tutorialdb=# SELECT * from DEPARTMENT; id | dept | emp_id ----+-------------+-------- 1 | IT Billing | 1 2 | Engineering | 2 3 | Finance | 7 4 | Engineering | 3 5 | Finance | 4 6 | Engineering | 5 7 | Finance | 6(7 rows) Now, let us use the UNION clause in the SELECT statement to combine the two tables as shown below: SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID UNION SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; This would produce the following result: emp_id | name | dept --------+-------+-------------- 5 | David | Engineering 6 | Kim | Finance 2 | Allen | Engineering 3 | Teddy | Engineering 4 | Mark | Finance 1 | Paul | IT Billing 7 | James | Finance(7 rows) * * * ## UNION ALL Clause The UNION ALL operator is used to combine two SELECT statements that contain duplicate rows. By default, the UNION operator selects distinct values. If duplicate values are allowed, use UNION ALL. ### Syntax The basic syntax of UNION ALL is as follows: SELECT column1 [, column2 ] FROM table1 [, table2 ] UNION ALL SELECT column1 [, column2 ] FROM table1 [, table2 ] The WHERE condition here can be set to any expression as per your requirement. ### Example Now, let us combine the two tables mentioned above using the SELECT statement with the UNION ALL clause: SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID UNION ALL SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; This would produce the following result: emp_id | name | dept --------+-------+-------------- 1 | Paul | IT Billing 2 | Allen | Engineering 7 | James | Finance 3 | Teddy | Engineering 4 | Mark | Finance 5 | David | Engineering 6 | Kim | Finance 1 | Paul | IT Billing 2 | Allen | Engineering 7 | James | Finance 3 | Teddy | Engineering 4 | Mark | Finance 5 | David | Engineering 6 | Kim | Finance(14 rows)