I have a simple rule in deciding when to use a join or not.
If the information that you are getting will come from two tables, then you should use a join, otherwise other strategies like a sub-query would do just fine. Let's take a look at two cases below using the sample database design we have used in my previous post.
- Generate a list of employees (name and employee number only) that are married.
- Generate a list of employees (name and employee number) that are married with their date of marriage.
If we will look at the output information of these two cases, we will have these shown below:
If you will look at the difference between the two output information, in the first case, all the fields of our output are all coming from one table -- the EMPLOYEE table. In the other case, we can see that DOM is a field that is coming from another table -- the EMPLOYEE_SPOUSE table. We can already predict at this point that case#1 will not use a join, but case #2 will use a join.
When using a join, we are actually combining two tables together to form a bigger and/or wider table. Let us see what happens when we join EMPLOYEE and EMPLOYEE_SPOUSE tables.
There are things that happened when you join two tables together:
- The fields of each table are also combined together. The four (4) columns of the EMPLOYEE table and the three (3) columns were merged together in the result.
- Only records in the EMPLOYEE TABLE that has a match on ENUM (e.enum=es.enum) with the EMPLOYEE_SPOUSE table is part of the result. Investigate this, INVENTADO, PAUL is a record in the EMPLOYEE table but since his ENUM has no match with an ENUM in the EMPLOYEE_SPOUSE table, INVENTADO, PAUL is not part of our result. Logically, you can also say that he is not included primarily because he is not married and he is not listed in the other table -- EMPLOYEE_SPOUSE.
And going back to our case, since we don't want all the columns to be part of the output information, we only select the columns we wanted, given us the final SQL statement of
SELECT E.ENUM, E.NAME, ES.DOM
FROM EMPLOYEE E JOIN EMPLOYEE_SPOUSE ES
ON E.ENUM=ES.ENUM
It is that easy.
0 comments:
Post a Comment