Thursday 1 December 2011

Union in SQL


The SQL UNION is used to combine the results of two or more SELECT SQL statements into a single result. Each query statement should have same column structure: same number of columns, same or compatible data types and in same order. The columns in each SELECT statement must be in exactly the same order too. You have to keep the column order of all unionized SQL SELECT expressions uniform, otherwise you’ll get an error.

SQL UNION Query Syntax
SELECT ColumnName(s) FROM Table1  
UNION  
SELECT ColumnName (s) FROM Table2  
Note: By Default result  of UNION is distinct. If you want all record you have to use UNION ALL

SQL UNION Query Example

Table: TestingEmployee
EmployeeId
FirstName
LastName
203
Rishi
Rawat
204
Michel
Jones
205
Nathen
Astel
206
Monica
George
Table: DevelopmentEmployee
EmployeeId
FirstName
LastName
206
Monica
George
207
Anita
Paul
208
John
Pit
209
Jennifer
Bob
Select all the different employees in TestingEmployee and DevelopmentEmployee:
SELECT EmployeeId, FirstName, LastName FROM TestingEmployee
UNION  
SELECT EmployeeId, FirstName, LastName FROM DevelopmentEmployees  
The result will look like:
EmployeeId
FirstName
LastName
203
Rishi
Rawat
204
Michel
Jones
205
Nathen
Astel
206
Monica
George
207
Anita
Paul
208
John
Pit
209
Jennifer
Bob

Note: Record of EmloyeeID 206 is in both tables but in result it display only one time if we use UNION ALL then the result display duplicate records for EmployeeId 206

No comments:

Post a Comment