The UNION operator is used to combine the result-set of two or more SELECT statements.
- Every SELECT statement within UNION must have the same number of columns
- The columns must also have similar data types
- The columns in every SELECT statement must also be in the same order
Union Syntax:
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Example:
UnionSample1 table consists of 5 records
UnionSample2 table consists of 5 records
Below Syntax for Union combines the result set of above two tables and provide only distinct values
select U_Name from UnionSample1
Union
select U_Name from UnionSample2;
Screen shot of the output given below
Union All Syntax:
The 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;
Example:
Below Syntax for UnionAll combines the result set of above two tables and provide duplicate values also as shown below
select U_Name from UnionSample1 Union All select U_Name from UnionSample2;
select * from order_products1 where cust_name LIKE ‘%________d’;
Below screen shot will show the order name with alphabet ‘d’ after 8 characters.
The SQL GROUP BY Statement
The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”.
The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.
Syntax
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s)
ORDER BY column_name(s);
Example:
Employees table is shown below
The following SQL statement lists the number of employees in each department
select Department_ID , count(First_Name) from Employees group by Department_ID;
Output of the above statement given below
From the same above Group by query, we can sort the department_ID by doing order By and the syntax given below.
select Department_ID , count(First_Name) from Employees group by Department_ID order by Department_ID DESC ;
Screen shot of the sorted Department ID given below
The SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.
Syntax:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s)
HAVING condition ORDER BY column_name(s);
Example:
Employees table given below:
The following SQL statement lists the number of employees in each department. Only include departments with equal to or more than 90:
select Department_ID , count(First_Name) from Employees group by Department_ID having Department_ID>=90 ;
Screen shot of the output given below
