Performance of inner join compared to cross 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. Optimise PostgreSQL for fast testing
  11. Does the order of where clauses matter in SQL?
  12. Fastest way to remove non-numeric characters from a VARCHAR in SQL Server
  13. Normalize array subscripts so they start with 1
  14. Counting DISTINCT over multiple columns
  15. How to count occurrences of a column value efficiently in SQL?
  16. Favourite performance tuning tricks [closed]
  17. SQL primary key: integer vs varchar
  18. SQL performance: WHERE vs WHERE(ROW_NUMBER)
  19. Can an INNER JOIN offer better performance than EXISTS
  20. How to check which locks are held on a table
  21. Huge performance difference when using GROUP BY vs DISTINCT
  22. SQL Server 2008 paging methods?
  23. Get execution time of PostgreSQL query
  24. Performance of Cross join with WHERE clause compared to Inner join
  25. When using GETDATE() in many places, is it better to use a variable?
  26. IN vs. JOIN with large rowsets
  27. What is the fastest way to truncate timestamps to 5 minutes in Postgres?
  28. Challenge,how to implement an algorithm for six degree of separation?
  29. Entity Framework Skip/Take is very slow when number to skip is big
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment