Performance of Cross join with WHERE clause compared to Inner join

by

Cross Joins produce results that consist of every combination of rows from two or more tables. That means if table A has 6 rows and table B has 3 rows, a cross join will result in 18 rows. There is no relationship established between the two tables – you literally just produce every possible combination.

With an inner join, column values from one row of a table are combined with column values from another row of another (or the same) table to form a single row of data.

If a WHERE clause is added to a cross join, it behaves as an inner join as the WHERE imposes a limiting factor.

As long as your queries abide by common sense and vendor specific performance guidelines (i), I like to think of the decision on which type of join to use to be a simple matter of taste.

(i) Vendor Specific Performance Guidelines

  1. MySQL Performance Tuning and Optimization Resources
  2. PostgreSQL Performance Optimization

More Related Contents:

  1. How does database indexing work? [closed]
  2. select * vs select column
  3. Inner join vs Where
  4. Best way to test if a row exists in a MySQL table
  5. Condition within JOIN or WHERE
  6. INNER JOIN vs LEFT JOIN performance in SQL Server
  7. Equals(=) vs. LIKE
  8. Subqueries vs joins
  9. Multiple INSERT statements vs. single INSERT with multiple VALUES
  10. Why would an IN condition be slower than “=” in sql?
  11. Optimise PostgreSQL for fast testing
  12. Does the order of where clauses matter in SQL?
  13. Fastest way to remove non-numeric characters from a VARCHAR in SQL Server
  14. Normalize array subscripts so they start with 1
  15. Performance of inner join compared to cross join
  16. Counting DISTINCT over multiple columns
  17. Is a JOIN faster than a WHERE?
  18. Fastest way to determine if record exists
  19. SQL primary key: integer vs varchar
  20. SQL performance: WHERE vs WHERE(ROW_NUMBER)
  21. Can an INNER JOIN offer better performance than EXISTS
  22. Subquery v/s inner join in sql server
  23. How to check which locks are held on a table
  24. Huge performance difference when using GROUP BY vs DISTINCT
  25. SQL Server SELECT LAST N Rows
  26. Get execution time of PostgreSQL query
  27. When using GETDATE() in many places, is it better to use a variable?
  28. IN vs. JOIN with large rowsets
  29. What is the fastest way to truncate timestamps to 5 minutes in Postgres?
  30. IN vs OR of Oracle, which faster?

Leave a Comment