INNER JOIN

INNER JOIN

Why is this word INNER JOIN capitalized? Becuase I am shouting at you! No not really, it is because SQL commands are normally capitalized, just as Java code is normally is camel case.

I like to think of an INNER JOIN as a nice way to keep you code clean. But in reality it is not only a professional way to develop a SQL Query or Stored Procedure but it also a performance enhancer. From the by laws at IBM,
"Using the JOIN syntax will influence the query optimizer to leave the tables in the listed order when performing the join. When the WHERE clause method is used, the query optimizer will evaluate the tables being joined and reorder them if it determines that a different order would perform better."

So using the 3NF I am going to write a query, first using the WHERE clause and secondly using the INNER JOIN. I am not going to explain the WHERE clause, if you don't understand it, then go to a site such as the W3Schools.

What we will query is to display all the students that live in Ohio and are computer science students.

SELECT PERSON.FIRSTNAME, PERSON.LASTNAME FROM PERSON
WHERE PERSON.PERSONID = STUDENT.STUDENTID
WHERE STUDENT.DEPARTMENTID = DEPARTMENT.DEPARTMENTID
WHERE DEPARTMENT.DEPARTMENT = 'Computer Science'
WHERE PERSON.LOCATIONID = LOCATION.LOCATIONID
AND LOCATION = 'OHIO'

So that is the query using the WHERE statement exclusively. Does this all work? Not sure, I didn't create the database, but it should be pretty close, meaning it is logically correct. Next up is to write it implementing INNER JOINS.

SELECT PERSON.FIRSTNAME, PERSON.LASTNAME FROM PERSON
INNER JOIN PERSON.PERSONID ON STUDENT.STUDENTID
INNER JOIN STUDENT.DEPARTMENTID ON DEPARTMENT.DEPARTMENTID
INNER JOIN PERSON.LOCATIONID = LOCATION.LOCATIONID
WHERE DEPARTMENT.DEPARTMENT = 'Computer Science'
AND LOCATION = 'OHIO'

So that's it! Not only is using the INNER JOIN easier to read, but should be quicker in the process. Also, one more thing, for INNER JOIN; we don't have to use the INNER, it works fine with just JOIN!