SQL UNION and UNION ALL

UNION

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type.

Note: With UNION, only distinct values are selected.

SQL Statement 1 UNION SQL Statement 2;

The "Books" table:

Title	Author	Publisher	Year
الدورة الدموية	إبن النفيس	دار العلم	1650
Java 2	L. Johnston	Fast Press	2002
Linux and Unix	J. Sam	Fast Press	2004
Operating Systems	M. Stone	Coriolis	2005
Web Programming	K. Yariv	East Edition	2005
XML Language	M. Salim	Knowledge Press	2000

The "Papers" table:

Title	Author	Journal	Year
Garbage Collection	J. Schmidt	Modern Computing	2005
Parallel Computing	H. Saleh	High Technology	2003
Quantum Computation	M. Candor	Quanta Journal	2004
Serialization	J. Xue	High Technology	2004
XML Language	A. Khan	Programming journal	2002

Using the UNION Command

Example

List the title and author of all different documents (books and papers):

SELECT Title FROM Books UNION SELECT Title FROM Papers;

The result is:

Title

الدورة الدموية

Java 2

Linux and Unix

Operating Systems

Web Programming

XML Language

Garbage Collection

Parallel Computing

Quantum Computation

Serialization

Note: This command cannot be used to list all documents (books and papers). In the example above we have two documents with equal titles ("XML Language"), and only one of them is listed. The UNION command only selects distinct values.

UNION ALL

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

SQL Statement 1 UNION ALL SQL Statement 2;

Using the UNION ALL Command

Example

List all documents (books and papers):

SELECT Title FROM Books UNION ALL SELECT Title FROM Papers;

The result is:

Title

الدورة الدموية

Java 2

Linux and Unix

Operating Systems

Web Programming

XML Language

Garbage Collection

Parallel Computing

Quantum Computation

Serialization

XML Language

Test your SQL Skills

To test your SQL skills click here.