Combining Queries with UNION

SELECT columns FROM table1 UNION SELECT columns FROM table2; 

The UNION keyword is used to join the result of two or more queries together into a single dataset result.

In the following example, the query returns the names of both the authors from the book table and the borrowers from the person table:

SELECT author FROM book UNION SELECT name FROM person; 

All tables combined using the UNION operator must return the same number of columns. The column names from the first query are used on the eventual result setfor instance, the previous example returns all the names in a column named author, even though some of the data originated in the person.name column.

By default, only unique rows are returned when you combine queries using UNION. If you want every row to be returned, use UNION ALL.

The following example performs two queries on book. The first query finds the names of books in mint condition. The second finds books where the author's name begins with the letter M.

SELECT title FROM book WHERE cond = 'mint' UNION ALL SELECT title FROM book WHERE author LIKE 'M%'; 

The output is as follows. Using UNION ALL causes In the Night Kitchen to be displayed twice, as it meets both criteria. The same query using UNION would only return this book once.

+-------------------------------------+ | title                               | +-------------------------------------+ | In the Night Kitchen                | | Jacob Two-Two Meets the Hooded Fang | | In the Night Kitchen                | +-------------------------------------+