Search code examples
sql-servercross-join

How to write ON condition for CROSS JOIN in SQL SERVER

Unable to write Join condition using ON clause.

This is Working:

SELECT STUDENT.STD_NAME,CLASS_SUBJECT.SUB_NAME 
FROM STUDENT CROSS JOIN CLASS_SUBJECT 
WHERE STUDENT.CLS_ID=CLASS_SUBJECT.CLS_ID

This is not working:

SELECT STUDENT.STD_NAME,CLASS_SUBJECT.SUB_NAME 
FROM STUDENT CROSS JOIN CLASS_SUBJECT ON STUDENT.CLS_ID=CLASS_SUBJECT.CLS_ID
Solution

  • CROSS JOIN does not use an ON clause. It produces a cartesian product, matching all records from both tables. There are only very rare cases where you actually want this.

    If you want conditional record matching between tables (for example where key values match), you should use INNER JOIN or one of the OUTER JOIN variants (LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN).

    Combining CROSS JOIN with a WHERE clause for conditional record matching is possible, but using INNER JOIN is recommended in such cases.