MySQL GROUP BY Statement

MySQL 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.

GROUP BY Syntax

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

Demo Database

Below is a selection from the “Customers” table in the sample database:

Customers

MySQL GROUP BY Examples

The following SQL statement lists the number of customers in each city:

Example

SELECT COUNT(CustomerID), City
FROM Customers
GROUP BY City;

The following SQL statement lists the number of customers in each city, sorted high to low:

Example

SELECT COUNT(CustomerID), City
FROM Customers
GROUP BY City
ORDER BY COUNT(CustomerID) DESC;

Demo Database

Below is a selection from the “Orders” table in the Northwind sample database:

Orders

And a selection from the “Shippers” table:

Shipper

GROUP BY With JOIN Example

The following SQL statement lists the number of orders sent by each shipper:

Example

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;

MySQL UNION Operator

              MySQL UNION Operator

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;

UNION ALL Syntax

The UNIONoperator 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;

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Customers” table:

Customers

And a selection from the “Suppliers” table:

Suppliers

SQL UNION Example

The following SQL statement returns the cities (only distinct values) from both the “Customers” and the “Suppliers” table:

Example

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

SQL UNION ALL Example

The following SQL statement returns the cities (duplicate values also) from both the “Customers” and the “Suppliers” table:

Example

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

SQL UNION With WHERE

The following SQL statement returns the German cities (only distinct values) from both the “Customers” and the “Suppliers” table:

Example

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

Another UNION Example

The following SQL statement lists all customers and suppliers:

Example

SELECT 'Customer' AS Type, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;

MySQL Self Join

                              MySQL Self Join

A self join is a regular join, but the table is joined with itself.

In the previous sections, we have learned about the joining of the table with the other tables using different JOINS, such as INNER, LEFT, RIGHT, and CROSS JOIN. However, there is a need to combine data with other data in the same table itself. In that case, we use Self Join.

We can perform Self Join using table aliases. The table aliases allow us not to use the same table name twice with a single statement. If we use the same table name more than one time in a single query without table aliases, it will throw an error.

The table aliases enable us to use the temporary name of the table that we are going to use in the query. Let us understand the table aliases with the following explanation.

Suppose we have a table named “student” that is going to use twice in the single query. To aliases the student table,

We can write it as:

Select … FROM student AS S1   
INNER JOIN student AS S2;  

Self Join Syntax

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

T1 and T2 are different table aliases for the same table.

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Customers” table:

Customers

MySQL Self Join Example

The following SQL statement matches customers that are from the same city:

Example

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

MySQL CROSS JOIN Keyword

MySQL CROSS JOIN Keyword

The CROSS JOIN keyword returns all records from both tables (table1 and table2).

MySQL CROSS JOIN is used to combine all possibilities of the two or more tables and returns the result that contains every row from all contributing tables. The CROSS JOIN is also known as CARTESIAN JOIN, which provides the Cartesian product of all associated tables. The Cartesian product can be explained as all rows present in the first table multiplied by all rows present in the second table. It is similar to the Inner Join, where the join condition is not available with this clause.

CROSS JOIN Syntax

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

Demo Database

In this tutorial we will use the well-known sample database.

Below is a selection from the “Customers” table:

Customers

And a selection from the “Orders” table:

Orders

MySQL CROSS JOIN Example

The following SQL statement selects all customers, and all orders:

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders;

Note: The CROSS JOIN keyword returns all matching records from both tables whether the other table matches or not. So, if there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.

If you add a WHERE clause (if table1 and table2 has a relationship), the CROSS JOIN will produce the same result as the INNER JOIN clause:

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders
WHERE Customers.CustomerID=Orders.CustomerID;

MySQL RIGHT JOIN Keyword

MySQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records (if any) from the left table (table1).

RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Demo Database

In this tutorial we will use the well-known sample database.

Below is a selection from the “Orders” table:

Orders

And a selection from the “Employees” table:

Employees

MySQL RIGHT JOIN Example

The following SQL statement will return all employees, and any orders they might have placed:

Example

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

Note: The RIGHT JOIN keyword returns all records from the right table (Employees), even if there are no matches in the left table (Orders).