SQL Simple -> Simple join between two tables

If our task is to retrive pieces of information stored in different tables using single query statement, we will use join between two or more tables. Let's see example where we want to retrive Departemnt name in addition to the information stored in Employees table. At first we pick up two tables, give them aliases and chose certain fields from each table. After this we specify Join condition in WHERE clause, that in our case will be based on the value of DeptNo column.

SELECT a.EmpNo, a.DeptNo, a.FirstName, a.LastName, a.Gender, b.DeptName AS Departement
FROM Employees AS a, Departments AS b
WHERE a.DeptNo = b.DeptNo

This query when executed will return result like this:
EmpNoFirstNameLastNameGenderDepartement
1johnsilverMmanagement
2gretgarboFmanagement
3lechenMlogistic
14suzanshpeerFlogistic
4danieldefoMadvertizing
..........

The possible way to retriave comlpete list of columns is to use sighn [*] instead of specific names. For example, if want Departement name, and ALL columns from Employees table:
SELECT b.DeptName AS Departement, a.*
FROM Employees AS a, Departments AS b
WHERE a.DeptNo = b.DeptNo

Result will look like this:
DepartementEmpNoDeptNoFirstNameLastNameGendercity...
management11johnsilverMlondon...
management21gretgarboFberlin...
logistic32lechenMberlin...
logistic142suzanshpeerFberlin...
advertizing43danieldefoMrome...

In many DBMS systems there is a possibilty to save retrieved data set into a new table. In MS-Access you just need to add INTO clause before FROM. The newlly created table will inherit datatypes from the source tables involved:
SELECT b.DeptName AS Departement, a.* INTO Employees_New
FROM Employees AS a, Departments AS b
WHERE a.DeptNo = b.DeptNo



sqlexamples.info