SQL and Command LEFT JOIN

Content of the lesson:

LEFT JOIN

We are going to learn about more advanced usage of the command SELECT in SQL. It can be used to connect more tables using the command LEFT JOIN. The SQL command JOIN is used to get data from two or more tables which have any relation.

We will use the same database as in the previous lesson but we have to add more tables, concretely renome_ccathegory and renome_treporter. The whole database can be downloaded in the file databaze.txt.

Structure of Table renome_ccathegory

We will create a few SQL queries for the online school newspapers database. The first table to be connected to our database of articles (renome_tarticle) is the renome_ccathegory. This table contains list of categories and consists of these attributes (not all attributes are listed, only the important ones):

• idtarticle - primary key of the table - it contains an integer value which identifies every row (this value is controlled by the property auto_increment - number greater by one than the last added value is automatically used)
• name - name of category
• mysort - this attribute is used to sort categories inside the menu

The structure of our table is illustrated in the following image.

Structure of Table renome_treporter

The second table to be connected to our database of articles (renome_tarticle) is the renome_treporter. This table contains a list of authors and consists of these attributes (not all attributes are listed, only the important ones):

• idcreporter - primary key of the table - it contains an integer value which identifies every row (this value is controlled by the property auto_increment - number greater by one than the last added value is automatically used)
• name - first name
• surname - last name

The structure of our table is illustrated in the following image.

Relation between Tables

When connecting more tables inside a database, you have to define their relation (the way how they should be connected). To be able to connect two tables, they should have one common value at least.

The table renome_tarticle contains all articles and the number of category where each article belongs is stored inside the column idccathegory. These numbers are linked to names of categories inside the column name of the table renome_ccathegory

The table renome_tarticle contains the number of author inside the column idcreporter. The complete list of authors is located inside renome_treporter where personal details of each author are stored.

The relation between tables inside our database is illustrated in the following image:

If you want to find out the name of category where each article belongs, you have to get data from both tables using the SELECT command. You need to get the headline from renome_tarticle and the name of category from renome_ccathegory. The command JOIN can help you achieve this situation.

Types of Command JOIN

We have several types of JOIN commands in SQL. The most used ones are:

• INNER JOIN - this command returns rows only if values in both tables are identical
• LEFT JOIN - this command returns all rows from the left table, even if there are no identical values in the right table
• RIGHT JOIN - this command returns all rows from the right table, even if there are no identical values in the left table

The basic principle of every JOIN command will be explained using a smaller database (you can download it here) which contains details about students and optional subjects. After this example we will return to our database and use the LEFT JOIN command inside it.

The database of optional subjects consists of two tables. The structure is illustrated in the following image:

INNER JOIN

The first type of the command JOIN is the command INNER JOIN. This command connects two tables and displays only data identical in both tables. In our case, when we assign the name of optional subjects to the abbreviations in the table of students, only those students will be displayed who chose an optional subject which is listed in the table of optional subjects.

SELECT
    studenti.id,
    studenti.jmeno, 
    studenti.vp, 
    predmety.nazev
FROM 
    studenti 
INNER JOIN 
    predmety ON studenti.vp = predmety.zkratka

The result is shown in the following image. Jana Nová is not displayed because she did not choose any optional subject and Michal Dvořák is also not displayed because his subject is not defined in the list.

LEFT JOIN

The next type of JOIN commands is the command LEFT JOIN. This command connects two tables and displayes all data from the left table (before command LEFT JOIN - in our case the table studenti) and assigns data from the right table to data from the left table. If no corresponding data is found, the command adds void values (NULL). This command will display the name of optional subject to each student. In case that the name of subject is not found, a void value (NULL) is displayed.

SELECT
    studenti.id,
    studenti.jmeno, 
    studenti.vp, 
    predmety.nazev
FROM 
    studenti 
LEFT JOIN 
    predmety ON studenti.vp = predmety.zkratka

The result of the query with LEFT JOIN command is displayed here:

Using the Command LEFT JOIN - Samples

The following queries demonstrate the usage of LEFT JOIN command. All following queries are linked to our database of school newspapers (you can download the database at the beginning of this lesson). You can check all results by running the queries in your database editor.

Query 1

This query will display headlines of all articles from category 2 and will add the name of category to them.

The command LEFT JOIN is used to connect the tables. The condition WHERE is added on the top of that to display only articles from category 2.

SELECT
    renome_tarticle.heading,
    renome_ccathegory.name
FROM 
    renome_ccathegory
LEFT JOIN 
    renome_tarticle ON renome_tarticle.idccathegory = renome_ccathegory.idccathegory
WHERE 
    renome_ccathegory.idccathegory = '2'

Query 2

This query will display headline, creation date, view count and name of category for every article from category 1, 3 or 5 and will sort the results by view count.

The command LEFT JOIN is used to connect the tables again. The command WHERE is more complex because we want to choose more categories (conditions are connected with the operator OR). The last part of the query is the command ORDER BY which sorts data according to selected column (DESC sets descending order).

SELECT
    renome_tarticle.heading, renome_tarticle.createdate, renome_tarticle.viewedcount, renome_ccathegory.name
FROM
    renome_tarticle
LEFT JOIN
    renome_ccathegory ON renome_tarticle.idccathegory = renome_ccathegory.idccathegory
WHERE
    ((renome_tarticle.idccathegory = '1') OR (renome_tarticle.idccathegory = '3')
    OR (renome_tarticle.idccathegory = '5'))
ORDER BY
    renome_tarticle.viewedcount DESC

Query 3

This query displays headline, name of category and the whole name of author.

The command LEFT JOIN is used twice in this query because we define two relations for the table renome_tarticle (connection with the table renome_ccathegory and connection with the table renome_treporter) according to the structure of our database which is displayed at the beginning of this lesson. The name of category from the table renome_ccathegory is assigned to the table renome_tarticle as well as the name of author from the table renome_treporter.

SELECT
    renome_tarticle.heading, renome_ccathegory.name, renome_treporter.name, renome_treporter.surname
FROM
    renome_tarticle
LEFT JOIN
    renome_ccathegory ON renome_ccathegory.idccathegory = renome_tarticle.idccathegory
LEFT JOIN
    renome_treporter ON renome_treporter.idcreporter = renome_tarticle.idcreporter        

Additional Texts

Questions

1. How can you use the JOIN command in SQL?
2. Describe differences between commands LEFT JOIN and INNER JOIN.