Content

• SQL union
• SQL group by
• SQL having
• SQL insert into select
• SQL stored procedures

SQL union

• The UNION operator is used to combine the result-set of two or more SELECT statements.
• Each SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in each SELECT statement must also be in the same order
• The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;

SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2

SQL group by

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.

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s)

SQL having

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;

---

SQL insert into select

The INSERT INTO SELECT statement copies data from one table and inserts it into another table.

INSERT INTO SELECT requires that data types in source and target tables match

INSERT INTO table2 SELECT * FROM table1 WHERE condition

The existing records in the target table are unaffected

---

SQL stored procedures

• A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
• So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

Execute the stored procedure above as follows:

EXEC SelectAllCustomers;