What if you were joining two tables, or even three tables, and you left off the join conditions? The result would be a Cartesian product. Every row of each table in the FROM clause would be joined with every row of the other tables. If one table had 15 rows, and a second table had 21 rows, a Cartesian product of those two tables would produce 315 rows in the result set of the query. Needless to say, it can be a big problem when you have three or more tables with no join conditions specified.
Cartesian Product A join between two tables where no join condition is specified, and as a result, every row in the first table is joined with every row in the second table.
Note Partial Cartesian products are produced when a query with n tables has less than n-1 join conditions between tables.
Needless to say, Cartesian products are used quite infrequently in SELECT statements, but they can be useful in very specific situations. For example, a Cartesian product of the EMPLOYEES table and the COUNTRIES table could give Janice a way to produce a checklist in a spreadsheet to note when a particular employee has visited one of the countries where Scott's widget company has a field office or distribution center. If employee visits to other offices were tallied in another table, then the Cartesian product could be joined to the new table as a running total of visits by employees to other offices.
Pre-Oracle9i Cartesian Product Syntax
Janice decides that the employee/country visit idea has some merit, and she experiments with some queries to generate the combinations of employees and countries using a Cartesian product query:
2675 rows selected.
Oracle9i Cartesian Product Syntax
The same query using the Oracle9i syntax is similar, except that CROSS JOIN is used to separate the two tables that are queried to produce a Cartesian product. Janice changes the previous query to use the Oracle9i version:2675 rows selected.
Oracle DBA Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Oracle 11g Interview Questions|
|Oracle apps Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle ADF Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps Functional Interview Questions|
|Oracle Apps DBA Interview Questions||Oracle Workflow Interview Questions|
Oracle Dba Tutorial
Relational Database Concepts
Sql*plus And Isql*plus Basics
Oracle Database Functions
Restricting, Sorting, And Grouping Data
Using Multiple Tables
Advanced Sql Queries
Installing Oracle And Creating A Database
Creating And Maintaining Database Objects
Users And Security
Making Things Run Fast (enough)
Saving Your Stuff (backups)
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.