-1

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
Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
Chandra
  • 156
  • 3
  • 11
  • 3
    (INNER) JOIN ON 1=1 is CROSS JOIN. In some DBMSs you can use (INNER) JOIN without ON & it means CROSS JOIN. But it's not standard SQL. PS (Obviously--) This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy May 22 '19 at 10:50
  • 1
    Possible duplicate of [CROSS JOIN vs INNER JOIN in SQL Server 2008](https://stackoverflow.com/questions/17759687/cross-join-vs-inner-join-in-sql-server-2008) – philipxy May 22 '19 at 10:52
  • Please in code questions give a [mcve]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) – philipxy May 22 '19 at 11:03

1 Answers1

3

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.

Bart Hofland
  • 3,700
  • 1
  • 13
  • 22
  • @Bart Hofland Thank you for clarifying my doubt on using ON condition.Your answer 'CROSS JOIN does not use an ON clause' comforted me. – Chandra May 22 '19 at 11:16
  • 1
    @philipxy I agree. I have edited my answer and changed "record matching" to "conditional record matching" instead. I am not aware of any formal terminology, however. – Bart Hofland May 22 '19 at 12:19
  • 1
    You are using vague phrases that can be reasonably applied to what you are talking about but don't clearly indicate/say what you are talking about. It's not a matter of formal terms, it's a matter of (the effort of) using enough words to clearly & fully say what you mean. (Exercise: try to define what "record matching" & then "conditional" mean in a way that you could substitute the definitions for the terms--those are not obvious meanings.) You mean something like, if you want every left input row to be part of some output row, or, if you want at least one output row for every input row. – philipxy May 22 '19 at 18:11
  • Oh... Or maybe when you say "record matching" you are just talking about joins in general, inner or outer. Then I suppose you added "conditional" as in cross join is unconditional as in not coming with an ON. So maybe your use of "conditional" has tipped my first guess/impression reading this version when unbiased by the previous version into what you meant. But in general try to use enough words to be unambiguously clear. – philipxy May 22 '19 at 18:20
  • @philipxy I think I know what you mean. And I agree. I understand that my wording was probably not as clear and unambiguous as would be expected. I am sorry for that. I will think about a rephrase again. However, it will not be my intention to explain in detail what joins are. I just wanted to briefly and roughly point out the difference between cross joins and the various other joins. For a full and academically correct description of each join type, there are way better documentation sources available on the Internet than I could ever write down in a single simple answer. – Bart Hofland May 22 '19 at 21:29