Good Reasons to Use Unrequired Correlation Names
October 24, 2007 Ted Holt
Correlation names are alternate names given to tables in an SQL command. Sometimes correlation names are required. Other times they are optional, but helpful. I would go so far as to say that it is good practice to use correlation names as often as possible, even when they are not obligatory. Let me share two good reasons to do so.
But first, let’s review the basics of correlation names. A correlation name follows a table name in the FROM clause. The table name and correlation name must be separated by white space. You may also include the filler word AS between the two. I always include AS because the query sounds better when I’m reading it.
This query has no correlation name.
SELECT classid, instructor, building, room FROM schedule
Here’s one that assigns correlation name SK to the schedule table.
SELECT classid, instructor, building, room FROM schedule as sk
In this case, using the correlation name doesn’t buy me anything, so let’s look at a more complex query.
SELECT classid, classes.name, instructor, faculty.name, building, buildings.name, room FROM schedule LEFT JOIN classes ON classid = classes.id LEFT JOIN buildings ON building = buildings.id LEFT JOIN faculty ON instructor = faculty.id
There are four tables–count ’em! Three of them–Classes, Buildings, and Faculty–have common column (field) names of ID and Name. I had to qualify all instances of the ID and Name fields with the table names in order to eradicate ambiguity.
This is fine, but it can turn into an awful lot of typing. This leads us to one of the most common uses of correlation names. You can use correlation names as shorter names for tables (and views). Here’s the same query using b, c, and f as correlation names for the Buildings, Classes, and Faculty tables, respectively.
SELECT classid, c.name, instructor, f.name, building, b.name, room FROM schedule LEFT JOIN classes as c ON classid = c.id LEFT JOIN buildings as b ON building = b.id LEFT JOIN faculty as f ON instructor = f.id
The only time a correlation name is required is when one table is used more than once in a query. For instance, to find the names of professors who share classrooms, we might run this query:
SELECT s1.building, s1.room, f1.NAME, f2.NAME FROM schedule AS s1 JOIN schedule AS s2 ON s1.building = s2.building AND s1.room = s2.room AND s1.classid <> s2.classid JOIN faculty AS f1 ON s1.instructor = f1.id JOIN faculty AS f2 ON s2.instructor = f2.id ORDER BY 1, 2
In this query, we need to access the schedule table twice and the faculty table twice. Without correlation names, there would be no way to determine which instance of each table was being referenced.
I hope that covers the basics. Now, as I was saying, there are good reasons to use correlation names when correlation names are not required. Here are two.
First, using a correlation name ensures that the SQL interpreter catches a missing-comma error. For instance, look at this query and tell me how many columns of data it produces.
SELECT CLASSID PERIOD, BUILDING, ROOM INSTRUCTOR FROM schedule
Yes! That’s right! The query produces three columns of data!
The first column, ClassID, is renamed to Period. The Building column is second. The room number is listed in the third column, but it is renamed to instructor. Clearly, the author of this query intended to produce five columns of data, but omitted two commas.
Let’s look at the same query, mistakes included, with correlation names.
SELECT sked.CLASSID sked.PERIOD, sked.BUILDING, sked.ROOM sked.INSTRUCTOR FROM schedule as sked
SQL produces error SQL0104: Token . was not valid. Valid tokens: , FROM INTO. Adding the missing commas produces the correct output.
SELECT sked.CLASSID, sked.PERIOD, sked.BUILDING, sked.ROOM, sked.INSTRUCTOR FROM schedule as sked
I consider this an improvement, as I prefer syntax errors to logic errors any day of the week.
A second good reason to use correlation names when they are not required is to enhance the readability of a query. Look at the following query and tell me in which table Room is stored.
SELECT classid, classes.NAME, instructor, faculty.NAME AS facname, building, buildings.NAME AS bname, room FROM schedule LEFT JOIN classes ON classid = classes.id LEFT JOIN buildings ON building = buildings.id LEFT JOIN faculty ON instructor = faculty.id
Now look at this query, which is functionally equivalent to the preceding one, and answer the same question.
SELECT sk.classid, classes.NAME, sk.instructor, faculty.NAME AS facname, sk.building, buildings.NAME AS bname, sk.room FROM schedule AS sk LEFT JOIN classes ON sk.classid = classes.id LEFT JOIN buildings ON sk.building = buildings.id LEFT JOIN faculty ON sk.instructor = faculty.id
Did you decide that the second query was easier to understand? The longer the query, the more I appreciate the inclusion of syntactically unnecessary correlation names.
P. S. I know that the word unrequired is not in the dictionary. I used it anyway in order to shorten the title.
P. P. S. For your reference, here is the data I used in developing these queries.
CREATE TABLE BUILDING ( ID DEC (3,0), NAME CHAR(20)); ID NAME 23 Decthee Hall 25 Offuv Center 41 Narrow Hall 42 Vaux Hall CREATE TABLE CLASSES ( ID DEC (3,0), NAME CHAR(20)); ID NAME 101 Antarctic Literature 102 Pig Latin IV 103 Rodeo Appreciation 104 Antarctic History II 105 Cooking with Guppies CREATE TABLE FACULTY (ID CHAR (3), NAME CHAR (20)); ID NAME F01 Monella, Sal F02 Nootix, Herman F03 Andree, Polly F04 LeNoll, Ty F05 O'Var, Sam F06 Sillen, Penny F07 Doo, Billy F08 Fishul, Benny CREATE TABLE classes CLASSID DEC(3, 0), PERIOD CHAR (1), BUILDING DEC (3,0), ROOM DEC (4,0), INSTRUCTOR CHAR (3)) CLASSID PERIOD BUILDING ROOM INSTRUCTOR 101 A 41 320 F02 102 A 41 218 F03 103 B 41 212 F02 104 B 42 302 F05 105 C 41 165 F04 106 B 41 212 F06 107 B 42 302 F01 108 D 41 212 F07 109 D 41 320 F08