1z0-071 Oracle Database SQL – DISPLAYING DATA FROM MULTIPLE TABLES – JOINS

1. BASICS OF A JOIN – CARTESIAN PRODUCT OR CROSS JOIN

We have been reading data from one table. However, often a database developer would be required to fetch data from multiple tables. To do that, those tables need to be joined in the sequel. Let us look at the basics of a join. Here we have two tables called tab one and tab two. Tab one has has two columns, call one and call two. Tab two has two columns, call three and call four. And the data is like this one A, two B in tab one in tab two. Now, what will happen if I do select call one, call two, call three, call four from tab one. Comma tab two. I am basically selecting all the columns in both the tables. From both the tables. How will the data be fetched? First, Oracle will verify the SQL code.

One of the verifications is to make sure the columns in the select class are present in the tables where the data is selected from. OK, what are the tables that we are selecting? From tab one and tab two. What are the columns they have? Call one and call two. From tab one, call three and call four. From tab two. We now have the definitive set of columns based on the tables in the from class. Now the column names in the select class must be from that set. Otherwise the sequel will fail. For example, this sequel will fail because there is no call five in the column set from tab one and tab two. Now let us go back to the original SQL. As a part of validation, Oracle will look for call one in the set. Is it there? Yes.

Like that it will look for other columns as well and all are there. Now, if you look at the SQL, it shows that we are just fetching data from this table. The result we are not passing any other conditions. How will Oracle react? It takes one table first and takes its first row and joins it with every row of the other table. For example, it may take tab one as the first table. Then it reads its first row, then produces an output by joining that row with all the rows in the second table. This would result in this one A joined with two D, three E and four F. So a total of three rows. Now the control will go to the second row of tab one table. Then it will join that row to every rows of tab two table. It will result in this two B. So a total of six rows, which is actually two times three.

That is two as in two rows here and three as in three rows here. This type of join is called as a cartesian product in mathematical terms or cross join. In relational database terms, a cartesian product or cross join is a join between two or more tables without any conditions. The result will always have number of rows, which is equal to the product of the number of rows in the tables involved in the joining. For example, a cross join of two tables with just 1000 rows each will bring 1 million rows in the result. So a cartesian product or cross join must be generally avoided as it may overload the database system. Mostly, a cartesian product occurs by accident when the developer forgets to include a proper join condition.

We will talk about the proper joint conditions later. However, the database organization team will usually be wary of a cartesian product as they may slow down the database system. If they see the above SQL, they might think that the developer who coded this SQL might have actually missed the proper join condition. But there may be situations where the developer might actually need a cartesian product and they don’t want the DBS to be alarmed about it. In that case, it is advisable to modify the SQL like this select call one, call two, call three, call four from tab one. Cross join tab two. When you explicitly mention the term crossjoy in, everyone will know that the resulting cartesian product is intentional and the developer knows what he or she is doing.

2. BASICS OF A JOIN – AMBIGUOUS COLUMN NAMES

We have another table called tab three with columns call four and call five. It has data like this. Now I do select call three, call four, call four, call five from tab two. Tab three. What will happen? We have a call for here and a call for here too. We have two columns with same names. Oracle will give an error saying column ambiguously defined because it doesn’t know which column we actually meant. To avoid such ambiguity, we can qualify the columns with the table name in front of it. This way Oracle will know which tables column is being referred.

But what if my tables names are large like these? Can we write this huge SQL not needed. We can give a short name to the tables in the from class and use them in front of the column names in the select class. Like this here. I gave a short alias for this table as Lt two and Lt three for this table. And I am using those aliases in front of the column names in the select class. This keeps the SQL more readable while preventing any ambiguity. So if we go back to the original SQL, we can write it like this if needed t two and T three are aliases for tables tab tab two and tab three respectively.

3. DEMO – CARTESIAN PRODUCT OR CROSS JOIN

Creating tab one. Inserting the data. Tab two. Inserting the data. Committed. Select Star from tab one. Start from tab two. Select call one, call two, call three. Call four from tab one, tab two and using cross join, the important thing is two rows here, three rows here and across. Giant R a Cartesian product brings two times three, six rows. Thank you for watching.

4. DEMO – AMBIGUOUS COLUMN NAMES

Creating table. Tab three. Inserting data. Committing the data. Select Star from tab three. Now I’m going to select call three, call four from tab two, call four, call from tab three and Oracle will give us an error. Even though I know I am selecting call four from tab two, we are actually not clearly specifying it. Column ambiguously defined. Now qualifying the columns with the table names. Tap two, call three, tap two, call four, tap three, call four, tap three, call five. It works fine. If needed, I can also add an alias. You can name the alias, whatever you want. I’m calling it T two and T three. It works fine. Thank you for watching.

5. EQUIJOIN – OLD METHOD

I want to bring the rows from both the tables where colonel, values of tab one is equal to call three. Values of tab two. Look at the condition here. It is equal to any joint doing. An equals two operation is called as an Equivjoint. Now let me write the sequel. Select t one. Call one. T one, call two. T two, call three. T two, call four. From tab one. T one comma tab two t two where T one, call one equals to T two. Col three. Here in the select class I choose the columns from both the tables. Also in the from class I gave aliases to the tables which is T one for tab one and t two for tab two.

In the where class, I’m saying bring me the rows where the values of t one, call one equals to T two. Col three. Now let us go one by one. Let us take the first row of tab one, call one’s. Value is one. Do we have a one in tab two’s, call three. No. So this row is ignored. Then the next one, the value is two. Do we have a two here? Yes. So both the rows match the condition. The output will be like this. Two B from tab one will be joined with two D of tab two. These will be joined together as a single row in the output. Let’s check it out. One A to B in tab one. This is tab two. Two B is joined with two.

6. EQUIJOIN – NEW ANSI STANDARD – JOIN ON CLAUSE

Video we saw that the join happening in the where class. However, Oracle decided to follow Ansi standards in the joint conditions where the joins actually happen in the from class. So this SQL can be replaced with a new Ansi format. I will be using ANC standards from now on. I showed the old method so that you wouldn’t be surprised if you see those codes in the legacy objects of your work environment. Here I am joining the tables using a join syntax. Then I have specified an on class followed by the columns and the condition for the join class. This is an example for join on class. Also remember, this is still an Equijian, but this is in ANC standard. Let’s check it out. This is the old join where the join happens in the where class. This is the ANC standard. Same results. T one is joined with t two. On t one. Column one equals to is the condition t to call three.

7. EQUIJOIN – NEW ANSI STANDARD – JOIN USING CLAUSE

If I have columns with same names in both the tables and I want to simplify my statement a little bit, then I would use join using class. Instead of specifying the column names explicitly, I would do join using just call one. This tells Oracle that there is a call one in tab one. There is also another call one in tab two and do an equi join using those. The important thing is you cannot qualify the joining column which is call one here with its table name t one. Call one or t two. Call one will not work and qualifying is not needed as well. Let us check it out. This is tab one. This is tab two. I’m going to create a table called tap 21 which would be a copy of tab two. Create table, tap 21 as select star from tap two. This will create a new table called tap 21 which would be a copy of tab two.

Tab 21 created select star from tab 21. OK, call three, call four and I am going to rename call three to call one. That’s the reason I am creating another table. Just to show you an example, as well as keep you familiar with these, create table and alter commands. We already looked into these commands in part one of this course. Again, alter table, tap 21. Rename column, call three to call one. Tap 21 altered select star from tap 21. It’s call one and call four. Now, this is the sequel with join on class and we are going to try join using and this will fail because I cannot qualify the column that is used in the using class with its table name name in front. So I’m doing this call one, call two and then call four. I am selecting if needed, you can select call one multiple times just to keep it similar to what we had before.

8. EQUIJOIN – NEW ANSI STANDARD – NATURAL JOIN CLAUSE

I want to simplify much more then I can use Natural Join. Like this sequel natural Join looks for columns with same names in both the tables and does an Equidign on all those columns. In this case it would be an equivalent between T one, call one and T two column one. Let us check it out. This is the join using statement. We know that call one is the column with the same name in both the tables. And this time I am going to avoid this class which is using class by using Natural Join class. So the minute I use Natural join class it will look for column names with same name and it will do a join. It can be one column or two column or more than two columns. As long as the name is same it will do a join on all those columns. Like the join using class. I cannot qualify the join columns with table names. It fails. If I do not qualify then it will work.

9. NON-EQUIJOINS

Non equivalents here the conditions are anything other than equals to in this sequel, I want these tables to be joined where t one call one is less than t two call three. Let us take the first row. In tab one is one less than any of the values here. Yes, one is less than all the values here. So the row one A will be joined with all the rows in tab two let us go to the second row. Is two less than any of the values here. Yes, two is lesser than three and four. So two B will be joined with three E and four F. Let us check it out. The condition is t one is lesser than t two col three which is a non Equi giant as this doesn’t involve an equals to condition. So one A joined with two D, three E, four E F and two B joined with three E and four.

10. OUTER JOINS – LEFT, RIGHT and FULL OUTER JOINS

It brings the rows that match the condition from both the tables. Any joint that brings the rows that meet the joint condition is called as inner join. However, there may be situations where apart from the rows that meet the condition, we may need the rows that do not meet the condition two that can be accomplished by outer join. For example, apart from this row which meets the condition, I may need the other rows from tab one. Since tab one RT one is on the left side of the join, I can accomplish this by specifying the left outer join keyword.

This will bring all other rows from tab one in the result and we’ll put null entries for the corresponding columns from tab two, this is called left outer join. Now, if I want to bring the rest of the rows from tab two, I will then specify right outer join in the sequel. Apart from the row that meets the condition, this will also bring the rest of the rows from tab two, this will also put null entries in the corresponding column values of tab one. Or, if I need the rest of the rows from both the tables, then I will do a full outer join. Apart from the row that meets the condition, this will also bring the rest of the rows from both tab one and tab two.

11. DEMO – OUTER JOINS – LEFT, RIGHT and FULL OUTER JOINS

This is the regular join. Two b and 2D. They match the condition. Two b are from tab one. 2d are from tab two. I’m doing left outer join. It brought one A from tab one and put null values for the corresponding columns of tab two, which are call three and call four. I’m doing right. Outer join apart from two b and brought three E and four E from tab two and put null values here for tab one. I’m doing full outer join. It brought two b and 2D also brought three E and four F from tab two. Put null values for tab one and one A from tab one. Put null values for tab two.