SQL joins – multi-table queries

Most of you should be familiar with basic queries, SELECTS, UPDATES and DELETES using one table. But to harness the true power of relational databases it is vital to master queries using more than one table. This article will introduce you to database joins – queries using 2 or more tables.

This article assumes you know how to perform basic SQL queries. If you don’t, I suggest you read Simple SQL: Getting Started With SQL first. A warning though – SQL implementations are notoriously non-standard, with almost every DBMS having its own extensions, as well as exclusions, especially when it gets to the realm of inner and outer joins! And version by version, they change. So, although most of these examples should work with most implementations, they don’t work with all. The final word should come from the documentation of your particular database installation.

Let’s do a quick recap for those who may be unsure. We will perform queries on the table below, containing data about tourist guides. The table is defined with:

CREATE TABLE tour_guides(
       employee_number INT NOT NULL,
       employee_name VARCHAR(100),
       hourly_rate INT,
       PRIMARY KEY(employee_number));

To add records into this table, we use:

INSERT INTO tour_guides(
            employee_number,
            employee_name,
            hourly_rate) 
       VALUES(981,'Xoliswe Xaba',25)

INSERT INTO tour_guides(
            employee_number,
            employee_name,
            hourly_rate) 
       VALUES(978,'Siyabonge Nomvete',30)

INSERT INTO tour_guides(
            employee_number,
            employee_name,
            hourly_rate) 
       VALUES(942,'Jean-Marc Ithier',35)

INSERT INTO tour_guides(
            employee_number,
            employee_name,
            hourly_rate) 
       VALUES(923,'Tyrone Arendse',32)

Note that you can also do a shortcut INSERT statement, leaving out the fieldnames if the number and order of the fields is the same, e.g:

INSERT INTO tour_guides VALUES(982,'Mathew Booth',25)

I don’t suggest using the shortcut however, as, particularly if you’re doing your SQL from within an application, the table structure may change, and then the SQL may no longer be valid. For example, I may add another field, such as months_employed. Now, the above INSERT statement will not work. You will get an error something like

Column count doesn't match value count at row 1

If you had written the statement as:

INSERT into tour_guides(
            employee_number,
            employee_name,
            hourly_rate) 
       VALUES(982,'Mathew Booth',25)

it would have worked. For this reason, I suggest you don’t use the shortcut – it makes your queries much less flexible, and less able to survive changes in table structure.

After the above, your table will looks as follows: Now the table contains:

To delete data from the table is also easy. Perhaps we have entered Xoliswe Xaba prematurely, as he has not accepted our generous offer of employment, and taken another job. To remove him, we use the statement:

DELETE FROM tour_guides WHERE employee_number=981

We use the employee number, and not the employee name, as according to our table definition the employee number is unique (the primary key). It is impossible for there to be another employee 981, while, unlikely as it may seem, there may, at least in theory, be another Xoliswe Xaba. This is why we almost always create a field that contains a unique code for each table.

Let’s assume we need to know how long someone has worked for us, so we add the months_employed field talked about earlier. We use:

ALTER TABLE tour_guides ADD months_employed int

After the DELETE, and the creation of the new field, the table now looks as follows:

See how all the months_employed values have defaulted to zero. Numeric fields, unless you specify otherwise, automatically default to zero. To start adding the months_employed, we use the UPDATE statement, as follows:

UPDATE tour_guides 
       SET months_employed=6 
       WHERE employee_number=978

UPDATE tour_guides 
       SET months_employed=12 
       WHERE employee_number=942

UPDATE tour_guides 
       SET months_employed=6 
       WHERE employee_number=923

Now our table contains the following:

All of this putting data in, changing and taking out is of course of no use unless you can retrieve the data. This is done with the SELECT statement. For example,

SELECT employee_name FROM tour_guides

returns

while

SELECT employee_name FROM tour_guides WHERE hourly_rate>30

returns

If all this SELECT stuff is a bit over your head, and you’re unsure of the powerful ways it can limit and perform calculations on the data, I suggest you first read a more introductory article, such as Simple SQL: Getting Started With SQL