Concrete example
Let's assume that we have two tables in a database, employee, and salary, with the following data:
employee: +----+---------+----------+ | id | name | department| +----+---------+----------+ | 1 | John | Sales | | 2 | Jane | Marketing | | 3 | Bob | Sales | | 4 | Alice | Marketing | +----+---------+----------+
salary: +----------+--------+ | employee_id | salary | +----------+--------+ | 1 | 50000 | | 1 | 55000 | | 2 | 65000 | | 3 | 60000 | | 4 | 70000 | +----------+--------+
When we run the following SQL query on this data:
SELECT e.name, e.department, SUM(s.salary) AS total_salary FROM employee e JOIN salary s ON e.id = s.employee_id GROUP BY e.department, e.name
The result will be the following table:
+---------+-----------+-------------+ | name | department| total_salary| +---------+-----------+-------------+ | John | Sales | 105000 | | Jane | Marketing | 65000 | | Bob | Sales | 60000 | | Alice | Marketing | 70000 | +---------+-----------+-------------+
In this example, the input data is the employee and salary tables, and the output is a table with the name, department, and total salary of each employee, grouped by department. The (INNER) JOIN operation is used to combine the data from the two tables based on the id and employee_id columns, and the GROUP BY clause is used to group the results by the department and name columns. The aggregate function SUM is used to calculate the total salary for each group.
Other mainly used JOIN options are LEFT JOIN and RIGHT JOIN. LEFT JOIN selects all the rows from the left side table of the join and the matching records on the right side table whereas RIGHT JOIN selects all the rows from the right side table of the join and the matching records from the left side table. There is also a CROSS JOIN that can be used with or without the WHERE condition (ON). If it is used without the WHERE condition it produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, with the WHERE condition it functions like an INNER JOIN.
The next article is Introduction to Creating query dynamically