SQL Basics

• UNION combines two or more SELECT statements
• The SELECT statements may query the same table or different tables
• Place UNION between the queries

SELECT   <column1>,
         <column2>,		
         <column3>
FROM     <table1>
WHERE    <condition1>
UNION
SELECT   <column1>,
         <column2>,		
         <column3>
FROM     <table1>
WHERE    <condition2>;

UNION on one table

• A UNION on one table can also be expressed using a more elaborate WHERE clause
• UNION can simplify a query when filtering in WHERE becomes complicated
• Result column names come from the first query, including any aliases

• Every query in a UNION must return the same number of columns
• Keep corresponding columns in the same order; some DBMSs require this
• Corresponding columns must have the same or compatible data types

SELECT   <column1>,
         AVG(column2)
FROM     <table1>
WHERE    <condition1>
GROUP BY <column1>
UNION
SELECT   <column1>,
         AVG(column2)
FROM     <table2>
WHERE    <condition2>
GROUP BY <column1>;

UNION on two tables

• UNION removes duplicates by default. Use UNION ALL to keep them
• Use one ORDER BY for the combined result, after the final query
• Each query in a UNION can have its own GROUP BY and HAVING

Use UNION to select the customer names, contact names and email addresses for customers in states 'IL', 'IN' or 'MI', as well as those for the company 'Fun4All' (4)

-- Oracle / MySQL / SQL Server
SELECT   cust_name,
         cust_contact,
         cust_email
FROM     Customers
WHERE    cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT   cust_name,
         cust_contact,
         cust_email
FROM     Customers
WHERE    cust_name = 'Fun4All'; 

61. Use UNION to select vendor IDs, product IDs and product prices where the price is below 10 or vend_id is 'DLL01' or 'BRS01'. (9)

62. Return the same results as exercise 61 using a single SELECT statement (9)

63. What is wrong?

SELECT   prod_name
FROM     Products
ORDER BY prod_name
UNION
SELECT   cust_name
FROM     Customers
ORDER BY cust_name;

64. What is the name of the best-selling desktop computer of all time?
    🌞

    Easter is celebrated on the first Sunday after the first full moon following the spring equinox. The course connects the celebration with the Sun and Moon, reflected in the names Sunday and Monday.

    The spring and fertility goddess Ostara brings us to the rabbit, a symbol of fertility. From rabbit to hare, we arrive at the idea of Easter eggs laid by the Easter hare.

    An Easter egg can also be a joke or hidden message in software, a film or a website. Like real Easter eggs, these features are hidden and must be found. Now that you have found one, click it! All exercise answers will become visible. Returning to the Welcome page hides your Easter egg again.