Home / code / sql

select data from more then 1 table

There are 2 main techniques to do this.
- INNER JOIN, which is not support by all databases
- The second method
- OTHER JOIN

INNER JOIN

Here you can combine 2 tables if a there is a common field with the same values.

... FROM table1 INNER JOIN table2 ON table1.field1 operator table2.field2

Part Explanation
Table1, table2 Names of the tables where the data comes from.
field1, field2 The names of the common fields. If they don't have numeric values, the data-type must be the same, there name doesn't have to be the same. If the names are the same you'll need to specify: table.field
operator <, >, <=, >=, =,<>

SELECT field1, field2 FROM table1 INNER JOIN table2 ON table1.field1 = table2.field2

It is possible to link on more then 1 field: 

SELECT field FROM table1 INNER JOIN table2
ON table1.field1 operator table2.field1 AND
ON table1.field2 operator table2.field2 OR
ON table1.field3 operator table2.field3

And you can also link more then 1 table. 

SELECT fields
FROM table1 INNER JOIN 
(table2 INNER JOIN table3
ON table2.field2 operator table3.field3)
ON table1.field1 operator table2.field2

The second method

SELECT fields FROM table1, table2 WHERE table1.field1=table2.field2
Part Explanation
Table1, table2 Names of the tables where the data comes from.
field1, field2 The names of the common fields. If they don't have numeric values, the data-type must be the same, there name doesn't have to be the same. If the names are the same you'll need to specify: table.field

If you want to link more then 2 tables: 

SELECT fields
FROM table1, table2, ..tableN
WHERE table1.field1=table2.field2 and
table2.field2=.. and
table.. .field..= tableN.fieldN

OUTER JOIN

... FROM table1 [LEFT | RIGHT] JOIN table2 ON table1.field1 operator table2.field2
Part Explanation
table1, table2 The names of the tables of which the records should be combined
field1, field2 The names of the fields that should be combined. The field must have the same data-type and the same kind of data. They don't need to have the same name.
operator <, >, <=, >=, =,<>

With LEFT JOIN all the records from the first(left-)table are added, even if there is no common value in the second(right-)table.

With RIGHT JOIN all the records from the second(right-)table are added, even if there is no common value in the first(left-)table.

For example, You have a table departments(left-table) and a table employees(right-table).
If you want to select all the departments, even those without employees, you use LEFT JOIN.
If you want to select all the employees, even those without a department, you use RIGHT JOIN.

You can nest(insert) LEFT- or RIGHT JOIN inside INNER JOIN, but you can not nest INNER JOIN in LEFT- or RIGHT JOIN.

 

TOP

Latest script:

 

Books: