- Introduction To SQL
- SQL Syntax
- SQL SELECT Statement
- SQL SELECT DISTINCT Statement
- SQL WHERE Clause
- SQL AND, OR And NOT Operators
- SQL ORDER BY Keyword
- SQL INSERT INTO Statement
- SQL NULL Values
- SQL UPDATE Statement
- SQL DELETE Statement
- SQL TOP, LIMIT, FETCH FIRST Or ROWNUM Clause
- SQL MIN() And MAX() Functions
- SQL COUNT(), AVG() And SUM() Functions
- SQL LIKE Operator
- SQL Wildcards
- SQL IN Operator
- SQL BETWEEN Operator
- SQL Aliases
- SQL Joins
- SQL INNER JOIN Keyword
- SQL LEFT JOIN Keyword
- SQL RIGHT JOIN Keyword
- SQL FULL OUTER JOIN Keyword
- SQL Self Join
- SQL UNION Operator
- SQL GROUP BY Statement
- SQL HAVING Clause
- SQL EXISTS Operator
- SQL ANY And ALL Operators
- SQL SELECT INTO Statement
- SQL INSERT INTO SELECT Statement
- SQL CASE Statement
- SQL NULL Functions
- SQL Stored Procedures For SQL Server
- SQL Comments
- SQL Operators
- SQL CREATE DATABASE Statement
- SQL DROP DATABASE Statement
- SQL BACKUP DATABASE For SQL Server
- SQL CREATE TABLE Statement
- SQL DROP TABLE Statement
- SQL ALTER TABLE Statement
- SQL Constraints
- SQL NOT NULL Constraint
- SQL UNIQUE Constraint
- SQL PRIMARY KEY Constraint
- SQL FOREIGN KEY Constraint
- ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder;
- SQL DEFAULT Constraint
- SQL CREATE INDEX Statement
- SQL AUTO INCREMENT Field
- SQL Working With Dates
- SQL Views
- SQL Injection
- SQL Hosting
- SQL Data Types For MySQL, SQL Server, And MS Access
SQL UNION Operator
The SQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements.
- Every
SELECTstatement withinUNIONmust have the same number of columns - The columns must also have similar data types
- The columns in every
SELECTstatement must also be in the same order
UNION Syntax
UNION
SELECT column_name(s) FROM table2;
UNION ALL Syntax
The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:
UNION ALL
SELECT column_name(s) FROM table2;
Note: The column names in the result-set are usually equal to the column names in the first SELECT statement.
Practice Excercise Practice now
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 |
Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
And a selection from the "Suppliers" table:
| SupplierID | SupplierName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Exotic Liquid | Charlotte Cooper | 49 Gilbert St. | London | EC1 4SD | UK |
| 2 | New Orleans Cajun Delights | Shelley Burke | P.O. Box 78934 | New Orleans | 70117 | USA |
| 3 | Grandma Kelly's Homestead | Regina Murphy | 707 Oxford Rd. | Ann Arbor | 48104 | USA |
Practice Excercise Practice now
SQL UNION Example
The following SQL statement returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table:
Example
UNION
SELECT City FROM Suppliers
ORDER BY City;
Note: If some customers or suppliers have the same city, each city will only be listed once, because UNION selects only distinct values. Use UNION ALL to also select duplicate values!
Practice Excercise Practice now
SQL UNION ALL Example
The following SQL statement returns the cities (duplicate values also) from both the "Customers" and the "Suppliers" table:
Example
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;
Practice Excercise Practice now
SQL UNION With WHERE
The following SQL statement returns the German cities (only distinct values) from both the "Customers" and the "Suppliers" table:
Example
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;
Practice Excercise Practice now
SQL UNION ALL With WHERE
The following SQL statement returns the German cities (duplicate values also) from both the "Customers" and the "Suppliers" table:
Example
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;
Practice Excercise Practice now
Another UNION Example
The following SQL statement lists all customers and suppliers:
Example
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;
Notice the "AS Type" above - it is an alias. SQL Aliases are used to give a table or a column a temporary name. An alias only exists for the duration of the query. So, here we have created a temporary column named "Type", that list whether the contact person is a "Customer" or a "Supplier".
Practice Excercise Practice now
Products
Partner
Copyright © RVR Innovations LLP 2024 | All rights reserved - Mytat.co is the venture of RVR Innovations LLP