This page explores advanced data retrieval and aggregation techniques using SQL in MariaDB.
11. GROUP BY - Group Rows with Aggregate Functions
Purpose: The GROUP BY clause is used in conjunction with aggregate functions (like COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.
Syntax: SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1;
Examples:
SELECT country, COUNT(customer_id) AS total_customers FROM Customers GROUP BY country; SELECT department, AVG(salary) AS average_salary FROM Employees GROUP BY department;
12. HAVING - Filter Grouped Results
Purpose: The HAVING clause is used to filter groups based on a specified condition, similar to WHERE but applied to aggregated results rather than individual rows.
Syntax: SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1 HAVING aggregate_condition;
Examples:
SELECT country, COUNT(customer_id) FROM Customers GROUP BY country HAVING COUNT(customer_id) > 5; SELECT department, AVG(salary) FROM Employees GROUP BY department HAVING AVG(salary) > 60000;
13. JOIN (INNER JOIN) - Combine Rows from Two or More Tables
Purpose: The JOIN clause (by default, INNER JOIN) is used to combine rows from two or more tables based on a related column between them. It returns only the rows where there is a match in both tables.
Syntax: SELECT columns FROM table1 INNER JOIN table2 ON table1.matching_column = table2.matching_column;
Examples:
SELECT Orders.order_id, Customers.customer_name FROM Orders INNER JOIN Customers ON Orders.customer_id = Customers.customer_id;
14. LEFT JOIN (or LEFT OUTER JOIN) - Return All Rows from Left Table
Purpose: The LEFT JOIN returns all rows from the left table, and the matching rows from the right table. If there is no match in the right table, NULL values are returned for right table columns.
Syntax: SELECT columns FROM table1 LEFT JOIN table2 ON table1.matching_column = table2.matching_column;
Examples:
SELECT Customers.customer_name, Orders.order_id FROM Customers LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id;
15. RIGHT JOIN (or RIGHT OUTER JOIN) - Return All Rows from Right Table
Purpose: The RIGHT JOIN returns all rows from the right table, and the matching rows from the left table. If there is no match in the left table, NULL values are returned for left table columns.
Syntax: SELECT columns FROM table1 RIGHT JOIN table2 ON table1.matching_column = table2.matching_column;
Examples:
SELECT Customers.customer_name, Orders.order_id FROM Customers RIGHT JOIN Orders ON Customers.customer_id = Orders.customer_id;