{"id":73211,"date":"2014-03-03T18:05:56","date_gmt":"2014-03-03T18:05:56","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/oracle-for-absolute-beginners-part-4-multi-table-queries\/"},"modified":"2021-07-14T13:07:35","modified_gmt":"2021-07-14T13:07:35","slug":"oracle-for-absolute-beginners-part-4-multi-table-queries","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/oracle-for-absolute-beginners-part-4-multi-table-queries\/","title":{"rendered":"Oracle for Absolute Beginners: Part 4 – Multi-table queries"},"content":{"rendered":"

Part 1<\/a>, Part 2<\/a>, Part 3<\/a><\/p>\n

A wise man* once said: To build the Great Wall of China, you must start with a brick. In our previous articles we acquainted ourselves with our bricks; now it\u2019s time to build. If we consider what we\u2019ve learned so far \u2013 Select, Update, Insert, Delete \u2013 as unicellular organisms, what we\u2019re about to do next is multicellular, big and beautiful \u2013 it\u2019s like going from an amoeba to a bee, a butterfly, to Beyonc\u00e9.<\/p>\n

[*that wise man was me<\/i>]<\/p>\n

Consider a real-world requirement that we might have of our Addressbook database; since it contains a list of our friends and their phone numbers, we will naturally want to see a list of their names and their phone numbers. Ah, but that presents a problem. Our friends\u2019 names are in the FRIEND_NAME table, while their phone numbers are in the PHONE_NUMBER table. And complicating things further, we can only tell which number belongs to which friend by looking in the FRIEND_PHONE table. Aaargh!<\/i><\/p>\n

Joins<\/h5>\n

We could, of course, get the information by running a series of queries: one select to find our friends\u2019 names and their friend_id; a second to find the phone_id of the phone number linked to each friend in FRIEND_PHONE; and a third query to find the number from PHONE_NUMBER using the phone_id we identified in our second query. So yes, it can<\/i> be done. But hey, you can probably ride a unicycle across Siberia \u2013 but that didn\u2019t stop them from inventing the car.<\/p>\n

What we need are joins. We need a select statement that can query multiple tables at the same time.<\/p>\n

The syntax for a multi-table select statement is as follows:<\/p>\n

SELECT <comma-separated column list>\r\nFROM <comma-separated table list>\r\nWHERE <conditions><\/pre>\n
Let me translate that into an actual query for you.<\/p>\n
SELECT FIRST_NAME, LAST_NAME, PHONE_NUMBER\r\nFROM FRIEND_NAME, FRIEND_PHONE, PHONE_NUMBER;<\/pre>\n
Run that\u00a0and see what you get. You\u2019ve probably already guessed that I\u2019m setting you up, but it\u2019s important that we make these mistakes now, so we can learn about them. The above query will give you many, many rows that look identical; however, if you replace the column list with an asterisk (*) and rerun the query, you\u2019ll notice that the records aren\u2019t exactly identical, each has one column different.<\/p>\n
What we have here is a Cartesian Product<\/strong>, and you\u2019ve probably already guessed that it has something to do with our missing Where clause. You\u2019re right. Whenever we don\u2019t tell Oracle how our tables are related to each other it simply joins every record in every table to every record in every other table. This doesn\u2019t only happen when our Where clause is completely missing; the same thing would happen if we were joining 3 tables, but only included 2 in our Where clause or if we joined the tables ambiguously (always join using key columns where possible).<\/p>\n
So if you ever notice that your query is returning more rows that you anticipated, look for a Cartesian join. Got that? Good, let\u2019s continue. Run the following statement:<\/p>\n
SELECT FRIEND_ID, FIRST_NAME, LAST_NAME, PHONE_NUMBER\r\nFROM FRIEND_NAME, FRIEND_PHONE, PHONE_NUMBER\r\nWHERE FRIEND_NAME.FRIEND_ID = FRIEND_PHONE.FRIEND_ID\r\nAND FRIEND_PHONE.PHONE_ID = PHONE_NUMBER.PHONE_ID;<\/pre>\n
I set you up again; sorry. Running the query will result in the following error:<\/p>\n
ORA-00918: column ambiguously defined<\/pre>\n
The issue here is that if two or more columns of the same name (such as friend_id<\/em> in our select list) exist in two or more of our tables, we must always tell Oracle which one we are referring to every time we use that column name. We do this by preceding the column name with the table name. In fact, it is good practice to do this with all columns in a multi-table statement. Makes it easier to read.<\/p>\n
Although if you had to type out the full table names each time you referred to a column in a long statement, you\u2019ll soon be in hospital with RSI. To get around this problem, you can use table aliases.<\/b> These are short nicknames you can give to tables (to do this you follow the table name immediately with the alias in your From statement). Let me rewrite our query to illustrate this:<\/p>\n
SELECT FN.FRIEND_ID, FN.FIRST_NAME, FN.LAST_NAME, PN.PHONE_NUMBER\r\nFROM FRIEND_NAME FN, FRIEND_PHONE FP, PHONE_NUMBER PN\r\nWHERE FN.FRIEND_ID = FP.FRIEND_ID\r\nAND FP.PHONE_ID = PN.PHONE_ID\r\nORDER BY FN.FIRST_NAME, FN.LAST_NAME;<\/pre>\n
Run the above statement. It\u2019s perfect, isn\u2019t it?<\/p>\n
SYSDATE<\/h5>\n
Not quite.<\/p>\n
My data and yours might now be very different because of all the practising you\u2019ve been doing (you have<\/i> been practising, haven\u2019t you? These articles might be good, but you may as well be reading a Dan Brown novel if you don\u2019t put what you\u2019re learning into practise). However, when I run the query, I get 2 rows for Rachel \u2013 one for her current phone number and another for her previous one.\u00a0 But how can we tell which is which?<\/p>\n
Of course we can simply add fp.start_date<\/em> and fp.end_date<\/em> to our column list and look to see which record is current. Alternatively, we can add the condition \u201cAND FP.END_DATE IS NULL\u201d to our Where clause and exclude end-dated rows in that way. But what if Rachel had told us that she was changing her phone number next month and we\u2019d put in a future<\/i> end-date? Excluding all end-dated rows now would give us the wrong result.<\/p>\n
What we need to do is query against today\u2019s date. But we don\u2019t want to hard-code<\/a> the date into our query, in case we want to rerun the query next week. What we need is SYSDATE.<\/p>\n
Run the following query:<\/p>\n
SELECT SYSDATE\r\nFROM DUAL;<\/pre>\n
It should\u00a0return today\u2019s date. And no matter which day you run it, it should return the correct date.<\/p>\n
Now we can rewrite our query as:<\/p>\n
SELECT FN.FRIEND_ID, FN.FIRST_NAME, FN.LAST_NAME, PN.PHONE_NUMBER\r\nFROM FRIEND_NAME FN, FRIEND_PHONE FP, PHONE_NUMBER PN\r\nWHERE FN.FRIEND_ID = FP.FRIEND_ID\r\nAND FP.PHONE_ID = PN.PHONE_ID\r\nAND (FP.START_DATE IS NULL OR FP.START_DATE<= SYSDATE) \r\nAND (FP.END_DATE IS NULL OR FP.END_DATE > SYSDATE);<\/pre>\n
And that’s it. Perfect.<\/p>\n
NVL()<\/h5>\n
Actually, maybe we can tighten it a little more. We\u2019ve guarded against Cartesian joins, we\u2019re using table aliases, and comparing our dates to SYSDATE, but we can use a function named NVL() to tidy up this condition: and (fp.end_date is null or fp.end_date > sysdate).<\/i><\/p>\n
NVL() is a function used to test if a value is null and to replace it with an alternative value if it is. Its syntax is as follows:<\/p>\n
NVL(value, replacement_value)<\/pre>\n
The NVL function will return value<\/i> if it is not null, but will return replacement_value <\/i>if it is. If both value<\/i> and replacement_value <\/i>are null, NVL will return null. Value<\/i> and replacement\u00ad<\/i>_value<\/i> can be of whichever datatype you like.<\/p>\n
Let us use NVL in our query.<\/p>\n
SELECT FN.FRIEND_ID, FN.FIRST_NAME, FN.LAST_NAME, PN.PHONE_NUMBER\r\nFROM FRIEND_NAME FN, FRIEND_PHONE FP, PHONE_NUMBER PN\r\nWHERE FN.FRIEND_ID = FP.FRIEND_ID\r\nAND FP.PHONE_ID = PN.PHONE_ID\r\nAND NVL (FP.START_DATE,SYSDATE) <= SYSDATE \r\nAND NVL (FP.END_DATE,SYSDATE+1)  > SYSDATE;<\/pre>\n
Our query is a thing of beauty, but you must have noticed that it does not return a complete list of our friends. You may have already figured out why; it only returns friends that have a phone number. But what if we wanted to see a full list of our friends with a null if they do not have a phone number?<\/p>\n
To do this I\u2019ll need to tell you about outer joins.<\/p>\n
Outer Join<\/h5>\n
The joins we\u2019ve been using thus far are effectively simple inner<\/i> joins.\u00a0 When two tables are joined using a simple join (e.g. fn.friend_id = fp.friend_id) records in both tables must satisfy the condition to appear in our resultset. However, with an outer join we can ask Oracle to impose our rule on one of our tables and return nulls whenever the other table fails the test. In other words, we can say, we want to see all<\/i> of our friends (all records in friend_name), and we don\u2019t mind seeing nulls whenever they don\u2019t have a phone number.<\/p>\n
There are two types of outer joins; a left<\/i> outer join allows nulls in the second table in our join, while a right<\/i> outer join allows nulls in the first table (while showing all records from the table on the right).<\/p>\n
The syntax is as follows:<\/p>\n
SELECT <comma-separated column list>\r\nFROM  <table1>\r\n[left|right] outer join <table2>\r\nON <join condition>\r\n[WHERE clause];<\/pre>\n
Our query is a little complex since it joins three tables; you might find it more helpful if I illustrate the point by starting small, with only two tables \u2013 FRIEND_NAME and FRIEND_PHONE.<\/p>\n
SELECT FN.FRIEND_ID, FN.FIRST_NAME, FN.LAST_NAME, FP.PHONE_ID\r\nFROM FRIEND_NAME FN\r\nLEFT OUTER JOIN FRIEND_PHONE FP\r\nON FN.FRIEND_ID = FP.FRIEND_ID\r\nand NVL (FP.START_DATE,SYSDATE) <= SYSDATE\r\nAND NVL (FP.END_DATE,SYSDATE+1)  > SYSDATE\r\nORDER BY FN.FRIEND_ID;<\/pre>\n
Run the query and notice how the resultset now contains all your friends, including those who do not have a phone number.\u00a0 Notice how, by using a left<\/i> outer join, we got all the rows in friend_name (the table on the left of the join); change it to a right outer join and see how that changes the output.<\/p>\n
Interesting, isn\u2019t it?\u00a0 But we need to add a third table \u2013 PHONE_NUMBER \u2013 to our query to make it useful. Let me show you how to do that.<\/p>\n
SELECT FN.FRIEND_ID, FN.FIRST_NAME, FN.LAST_NAME, PN.PHONE_NUMBER\r\nFROM FRIEND_NAME FN\r\nLEFT OUTER JOIN FRIEND_PHONE FP\r\n   ON FN.FRIEND_ID = FP.FRIEND_ID\r\n   AND NVL (FP.START_DATE,SYSDATE) <= SYSDATE\r\n   AND NVL (FP.END_DATE,SYSDATE+1)  > SYSDATE\r\nLEFT OUTER JOIN PHONE_NUMBER PN\r\n   ON FP.PHONE_ID = PN.PHONE_ID\r\nORDER BY FRIEND_ID;<\/pre>\n
Joining multiple tables in this way is a little like baking a cake; you apply your joins layer by layer. (NB: I\u2019ve never baked a cake before, but I once saw someone bake a cake on TV, and I remember thinking, \u201cHey, that\u2019s just like writing an outer join query!\u201d)<\/p>\n
If you\u2019re up for a challenge, why don\u2019t you add a few more layers to our query? Expand the query to display our friends\u2019 addresses: to do this you\u2019ll need to join the FRIEND_ADDRESS and ADDRESS tables.<\/p>\n
While you\u2019re doing that, I\u2019ll go eat some cake. I\u2019m suddenly hungry for some reason.<\/p>\n
Next time we’ll look at PL\/SQL – functions and procedures.<\/i><\/p>\n
 <\/p>\n
Next Article:\u00a0Oracle for Absolute Beginners: Part 5 \u2013 PL\/SQL<\/a><\/h3>\n","protected":false},"excerpt":{"rendered":"
Part 1, Part 2, Part 3 A wise man* once said: To build the Great Wall of China, you must start with a brick. In our previous articles we acquainted ourselves with our bricks; now it\u2019s time to build. If we consider what we\u2019ve learned so far \u2013 Select, Update, Insert, Delete \u2013 as unicellular organisms, what we\u2019re about to…<\/p>\n","protected":false},"author":221907,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48366,5609,124952],"coauthors":[48557],"class_list":["post-73211","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-beginners-guide","tag-database-development","tag-redgate-deploy"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73211","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221907"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73211"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73211\/revisions"}],"predecessor-version":[{"id":88958,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73211\/revisions\/88958"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73211"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73211"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73211"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73211"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}