SQL Relations

• ‹ previous
• 6 of 6

Primary key is identifier of row in table. Foreign key is reference from one table to primary key of other table. SQL JOIN keyword is used to query data from two ore more tables, using relation columns.

Primary keys

Every table in database should have primary key. Primary key is column (or combination of columns), which has unique value for each row. In sample Chinook database, primary key columns are named as table with suffix "Id" - eg. for table Customer is primary key CustomerId - number of customer. Most of databases has convention, how to systematically call primary key columns.

Structure of table Customer

Foreign Keys

We want to store list of all invoices, with information about customer belonging to this invoice. We can create table Invoice and add all Customer columns to Invoice table. But better way is to add only reference to Customer.

Below are columns of table Invoice:

There is column named CustomerId, which determines, which customer is given invoice for. This database technique is also called normalization, thanks to this, if you want eg. to change Country of customer, you make change in Customer table and all invoices for this customer will automatically use new value of Country.

Querying related data

We want to query all invoices (only InvoiceData and Total columns) and related Customer first name an last name. This is typical apply of SQL JOIN command.

Syntax:

SELECT columns FROM table1
JOIN table2 ON table1.column = table2.column

JOIN command concatenates rows from given tables (result row can contain column from any joined table) and join rows using condition given in ON clause. In our example, JOIN will look like following:

SELECT Invoice.InvoiceDate, Invoice.Total, Customer.FirstName, Customer.LastName FROM Invoice
JOIN Customer ON Invoice.CustomerId = Customer.CustomerId

Qualified column names

In last example, we used selecting columns with notation table.column. This notation can be used allways, but it must be used when column names are ambigous.

Eg. if you try to run SQL command

SELECT CustomerId FROM Invoice
JOIN Customer ON Invoice.CustomerId = Customer.CustomerId

you became error "ambiguous column name: CustomerId" because CustomerId is in both tables Invoice and Customer. (thanks to ON condition, values Invoice.CustomerId and Customer.CustomerId are the some in all rows, but formally this is error and database engine requires to explicitly define source table)

Other join types

In above example, what happens with rows of Invoice table, which have CustomerId = NULL? If you use exactly the some code as above, these rows are not present in result set. Quite often this behaviour is not what we expect, than LEFT JOIN could be used.

SELECT Invoice.InvoiceDate, Invoice.Total, Customer.FirstName, Customer.LastName FROM Invoice
LEFT JOIN Customer ON Invoice.CustomerId = Customer.CustomerId

This example return also rows with Invoice.CustomerId = NULL, fields from customer (Customer.FirstName, Customer.LastName) in result set will have NULL value.

• INNER JOIN - the some as JOIN
• LEFT JOIN
• RIGT JOIN - inverse of LEFT JOIN (used quite rarely, more often joined tables are ordered so that LEFT JOIN can be used)
• OUTER JOIN - combination of LEFT and RIGHT JOIN
• CROSS JOIN - (cartesian product) - returns all rows from first table combined with all rows from seconds table, doesn't contain ON clause (it has not sense as all rows are returned)

• ‹ previous
• 6 of 6