SQL joins - multi-table queries
February 28, 2002
Most of you should be familiar with basic queries,
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:
To add records into this table, we use:
Note that you can also do a shortcut
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
If you had written the statement as:
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:
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:
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:
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,
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