It is very important to use an equal condition in the WHERE clause. Otherwise you get a product join. This means that one row of a table is joined to multiple rows of another table. A mathematic product means that multiplication is used.
The next join example uses a WHERE clause, but it only limits which rows participate in the join and does not provide a join condition:
The above output resulted from 1 row in the customer table being joined to all the rows of the order table. The WHERE limited the customer rows that participated in the join, but did not specify an equal comparison between the join columns. As a result, it looks like Billy placed five orders, which is not correct. So, be careful when using product joins because SQL answers the question as asked, not necessarily as intended.
When all rows of one table are joined to all rows of another table, it is called a Cartesian product join or an unconstrained product join. Think about this: if one table has one million rows and the other table contains one thousand rows, the output is one trillion rows (1,000,000 rows * 1,000 rows = 1,000,000,000 rows).
As seen above, the vast majority of the time, a product join has no meaningful output and is usually a mistake. The mistake is either that the WHERE clause is omitted, a column comparison is omitted for one of the tables using an AND, or the table is given an alias and the alias is not used (system thought it was an additional table without a comparison).
The next SELECT is the same as the one above, except this time the entire WHERE clause has been commented out using /* and */:
Since the join condition is converted into a comment, the output from the SELECT is a Cartesian product that will return 980 rows (10*70*14=980) using these very small tables. The output is completely meaningless and implies that every student is taking every class. This output does not reflect the correct situation.
Forgetting to include the WHERE clause does not make the join syntax incorrect. Instead, it results in a Cartesian product join. Always use the EXPLAIN to verify that the result of the join is reasonable before executing the actual join. The following shows the output from an EXPLAIN of the above classic Cartesian product join. Notice that steps 6 and 7 indicate a product join on the condition that (1=1). Since 1 is always equal to 1 every time a row is read, all rows are joined with all rows.
If you remember from, the EXPLAIN shows immediately that this situation will occur if the SELECT is executed. This is better than waiting, potentially hours, to determine that the SELECT is running too long, stealing valuable computer cycles, doing data transfer, and interfering with valid SQL from other users. Be a good corporate citizen and database user: EXPLAIN your join syntax before executing! Make sure the estimates are reasonable for the size of the database tables involved.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.