Foreign keys and joins¶

Foreign keys¶

In order to create relationships between tables, in addition to defining primary keys, you need to specify correlated foreign keys that will indicate the relationship between objects (ie they will point to the primary key of another table). A foreign key can be entered into the table using the structure:

CONSTRAINT name FOREIGN KEY (column1, column2, column3, ..., columnN) REFERENCES table_name (column1, column2, ..., columnN)

• name is the name of the constraint by which the foreign key will be identified
• columns defined after FOREIGN KEY relate to table foreign keys
• table_name specifies the name of the table the foreign key comes from
• the columns after the REFERENCES clause specify specific columns that are derived from the foreign keys that are created.

Create a foreign key¶

The foreign key can be created when creating the table:

CREATE TABLE Product
(
    Id INTEGER PRIMARY KEY,
    Name VARCHAR(20),
    ProducerId INTEGER, 
    CONSTRAINT producerId_fk FOREIGN KEY (ProducerId) REFRENCES Producer(Id)
);

Foreign keys can also be added to already existing tables. For this, the ALTER TABLE statement is executed. Its general form is as follows:

ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column1, column2, ..., columnN) REFERENCES table_name(column1, column2, ..., columnN);

A real-life example looks like this:

ALTER TABLE Product ADD CONSTRAINT producer_fk FOREIGN KEY (ProducerId) REFERENCES Producer(Id);

Removing a foreign key¶

Removing a foreign key that exists within a table is done with the ALTER TABLE statement:

ALTER TABLE Product DROP FOREIGN KEY producer_fk;

Advanced SELECT clause constructs¶

Retrieving data from several tables¶

Using the SELECT statement, it is possible to retrieve data from several tables. For this purpose, it is enough to enter the next names after the comma.

SELECT column1, column2, column3, ...,columnN FROM table1, table2, ..., tableN 
[WHERE condition]
...;

Such queries are often made by giving aliases to particular tables, e.g .:

SELECT k.Publisher, p.Name FROM Book AS k, Product AS p;

NOTE: The AS keyword, when specifying aliases, is optional . Moreover, if you do not give an alias to a given table, you can use the table name when selecting a column.

Joins¶

There are different types of table joins in SQL, including:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN.

For the following examples, we will use the following tables with data:

INNER JOIN¶

This join type allows you to join the table data like a normal SELECT command, including tables. The query construction looks like this:

SELECT column1, column2, ..., columnN FROM table1 [INNER] JOIN table2 [ON condition];

The ON clause defines a join condition.

For the example presented, the command will take the following form:

SELECT ProductId, Product.Name, Product.ProducterId, Producer.ProducerId, Producer.Name, Producer.Address FROM Product INNER JOIN Producer ON Product.ProducerId=Producer.Producer.Id;

The result of the command will return the intersection of both tables:

In case both tables have the same column names, you must specify a specific column by using the table name as a prefix.

LEFT JOIN¶

This type of join allows you to include the resulting data that is not related to the joined table. In short, if there are records in table1 that are not correlated with records intable2, they will be included in the join anyway, and the missing values will be filled with the values of NULL. The instruction scheme is as follows:

SELECT column1, column2, ..., columnN FROM table1 [LEFT] JOIN table2 [ON condition];

SELECT ProductId, Product.Name, Proukt.ProducerId, Producer.ProducerId, Producetn.Name, Producer.Address FROM Product LEFT JOIN Producer ON Product.ProducerId=Producer.Producer.Id;

The result will be the following dataset:

Thanks to the LEFT JOIN query, you can easily get data on products with an undefined manufacturer.

RIGHT JOIN¶

This type of join is analogous to LEFT JOIN, except that the result table includes rows from the second table that are not referenced by their counterparts in the first. The instruction scheme is as follows:

SELECT column1, column2, ..., columnN FROM table1 [RIGHT] JOIN table2 [ON condition];

For the command below:

SELECT ProductId, Product.Name, Product.ProducerId, Producer.ProducerId, Producer.Name, Producer.Address FROM Product RIGHT JOIN Producer ON Product.ProducerId=Producer.Id;

The result will be the following dataset:

FULL OUTER JOIN¶

This join allows you to get all the records from the two tables. Considering both records from table1 which have no counterpart in table2 and the other way around.

SELECT column1, column2, ..., columnN FROM table1 [FULL OUTER] JOIN table2 [ON condition];

For the command below:

SELECT ProductId, Product.Name, Product.ProducertId, Producer.ProducerId, Producer.Name, Producer.Address FROM Product FULL OUTER JOIN Producer ON Product.ProducerId=Producer.Id;

The result will be the following dataset: