The UNION operator is used to combine the result-set of two or more SELECT statements. Show
UNION Syntax
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2; UNION ALL SyntaxThe UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:
SELECT column_name(s) FROM table1 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. Demo DatabaseIn this tutorial we will use the well-known Northwind sample database. Below is a selection from the "Customers" table:
And a selection from the "Suppliers" table:
SQL UNION ExampleThe following SQL statement returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table:
SELECT City FROM CustomersUNIONSELECT City FROM Suppliers ORDER BY City; Try it Yourself »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! SQL UNION ALL ExampleThe following SQL statement returns the cities (duplicate values also) from both the "Customers" and the "Suppliers" table:
SELECT City FROM CustomersUNION ALLSELECT City FROM Suppliers ORDER BY City; Try it Yourself »SQL UNION With WHEREThe following SQL statement returns the German cities (only distinct values) from both the "Customers" and the "Suppliers" table:
SELECT City, Country FROM CustomersWHERE Country='Germany'UNION SELECT City, Country FROM SuppliersWHERE Country='Germany' ORDER BY City; Try it Yourself »SQL UNION ALL With WHEREThe following SQL statement returns the German cities (duplicate values also) from both the "Customers" and the "Suppliers" table:
SELECT City, Country FROM CustomersWHERE Country='Germany'UNION ALL SELECT City, Country FROM SuppliersWHERE Country='Germany' ORDER BY City; Try it Yourself »Another UNION ExampleThe following SQL statement lists all customers and suppliers:
SELECT 'Customer' AS Type, ContactName, City, CountryFROM Customers UNIONSELECT 'Supplier', ContactName, City, Country FROM Suppliers; Try it Yourself »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".
The _______ operator takes the results of two queries and returns only rows that appear in both result sets. Course Name: SQL -Structured Query Language
You’d like to display data from given columns (of a similar data type) from two tables in SQL. Example:There are two tables in our database: employee and customer. The employee table contains data in the following columns: id, first_name, last_name, and age.
The customer table contains data in the following columns: id, first_name, last_name, and age.
In one result set, let’s display the first name, last name, and age for all people in the database, both employees and customers. Solution 1:We’ll use UNION ALL to join data from columns in two tables. Here’s the query you’d write: SELECT first_name, last_name, age FROM employee UNION ALL SELECT first_name, last_name, age FROM customer;Here’s the result:
Discussion:Use the UNION ALL clause to join data from columns in two or more tables. In our example, we join data from the employee and customer tables. On the left of the UNION ALL keyword, put the first SELECT statement to get data from the first table (in our example, the table employee). On the right, use another SELECT statement to get data from the second table (in our example, customer). Remember that the selected data in both tables must be of the same data type in each column. For example, if the first column in the first SELECT is a string data type, the first column in the second SELECT must also be a string data type. If the second column in the first SELECT statement is an integer, the second column in the second table must also be an integer type. In the first query, we selected age (the age of the employee, which is an integer data type) for the third column. Therefore, the third column in the second SELECT is also an integer value; it’s the age of the customer. The second columns in both SELECT statements are the same data type. However, if the values are the same in both tables, they will be displayed multiple times; for example, ‘Charles Davis 21’ is shown twice in the result set. What if don’t want multiple identical records in the result table? In this case, use UNION. It is similar to UNION ALL, but it removes duplicate records. Look at the following example. Solution 2:Here’s the query that avoids duplicate records: SELECT first_name, last_name FROM employee UNION SELECT first_name, last_name FROM customer;Here’s the result of the above query:
Note: UNION ALL is faster than UNION, but UNION removes duplicate rows. The choice depends on the result data we need.
This set of Database MCQs focuses on “Relational Query Operations and Relational Operators”. 1. Using which language can a user request information from a database? a) Query b) Relational c) Structural d) Compiler View Answer Answer: a 2. Student(ID, name, dept name, tot_cred) In this query which attributes form the primary key? a) Name b) Dept c) Tot_cred d) ID View Answer Answer: d 3. Which one of the following is a procedural language? a) Domain relational calculus b) Tuple relational calculus c) Relational algebra d) Query language View Answer Answer: c 4. The_____ operation allows the combining of two relations by merging pairs of tuples, one from each relation, into a single tuple. a) Select b) Join c) Union d) Intersection View Answer Answer: b 5. The result which operation contains all pairs of tuples from the two relations, regardless of whether their attribute values match. a) Join b) Cartesian product c) Intersection d) Set difference View Answer Answer: b
Check this: Programming Books | Computer Science Books 6. The _______operation performs a set union of two “similarly structured” tables a) Union b) Join c) Product d) Intersect View Answer Answer: a 7. The most commonly used operation in relational algebra for projecting a set of tuple from a relation is a) Join b) Projection c) Select d) Union View Answer Answer: c 8. The _______ operator takes the results of two queries and returns only rows that appear in both result sets. a) Union b) Intersect c) Difference d) Projection View Answer Answer: b 9. A ________ is a pictorial depiction of the schema of a database that shows the relations in the database, their attributes, and primary keys and foreign keys. a) Schema diagram b) Relational algebra c) Database diagram d) Schema flow View Answer Answer: a 10. The _________ provides a set of operations that take one or more relations as input and return a relation as an output. a) Schematic representation b) Relational algebra c) Scheme diagram d) Relation flow View Answer Answer: b Sanfoundry Global Education & Learning Series – Database Management System. Next Steps:
 Subscribe to his free Masterclasses at Youtube & technical discussions at Telegram SanfoundryClasses. |
Which is the operator takes the result of two queries
Pos Terkait
Copyright © 2024 ketiadaan Inc.
