What is the difference between using a cross join and putting a comma between the two tables?

by

They return the same results because they are semantically identical. This:

select * 
  from A, B

…is (wince) ANSI-89 syntax. Without a WHERE clause to link the tables together, the result is a cartesian product. Which is exactly what alternative provides as well:

    select * 
      from A 
cross join B

…but the CROSS JOIN is ANSI-92 syntax.

About Performance

There’s no performance difference between them.

Why Use ANSI-92?

The reason to use ANSI-92 syntax is for OUTER JOIN support (IE: LEFT, FULL, RIGHT)–ANSI-89 syntax doesn’t have any, so many databases implemented their own (which doesn’t port to any other databases). IE: Oracle’s (+), SQL Server’s =*

More Related Contents:

  1. How do I UPDATE from a SELECT in SQL Server?
  2. Generate a resultset of incrementing dates in TSQL
  3. T-SQL Cast versus Convert
  4. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  5. What does =* mean?
  6. Correct use of transactions in SQL Server
  7. Why do you create a View in a database?
  8. Need to list all triggers in SQL Server database with table name and table’s schema
  9. How to select only the first rows for each unique value of a column?
  10. Know relationships between all the tables of database in SQL Server
  11. how does SELECT TOP works when no order by is specified?
  12. SQL Server search using like while ignoring blank spaces
  13. Calculate a Running Total in SQL Server
  14. Truncate (not round) decimal places in SQL Server
  15. Select statement to find duplicates on certain fields
  16. Getting only Month and Year from SQL DATE
  17. Dynamic Pivot Columns in SQL Server
  18. How to send email from SQL Server?
  19. How To Create Table with Identity Column
  20. How to run a stored procedure in sql server every hour?
  21. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  22. Calculate the number of records for each date between 2 dates
  23. T-SQL: How to use parameters in dynamic SQL?
  24. How to compare datetime with only date in SQL Server
  25. SQL server select distinct rows using most recent value only
  26. Split words with a capital letter in sql
  27. SQL poor stored procedure execution plan performance – parameter sniffing
  28. How do I create and query linked database servers in SQL Server?
  29. Eliminate and reduce overlapping date ranges
  30. scope_identity vs ident_current

Leave a Comment