I was watching the IBM SQL lecture on Coursera. And there is an example, Select * from Employees, Departments; The professor mentioned that the outcome could be a full join or Cartesian join. However, given my experience, I only have gotten the Cartesian join. Does anyone know when it will return the full join and depends on what condition?
2 Answers
Before the year 1992, the standard SQL syntax to produce the cartesian product of two tables was (note the absence of a WHERE clause):
select * from Employees, Departments If you add WHERE clause, you'll be adding a predicate that will reduce the number of selected rows, essentially defeating the purpose of producing a cartesian product.
Then SQL-92 defined a new [clearer] way of writing it using the CROSS JOIN clause:
select * from Employees cross join Departments Both syntaxes will work on pretty much all database engines, but I would strongly recommend the latter since is clearer to read and to debug.
As a side note, I would recommend the professor to modernize himself/herself. SQL-92 happened 27 years ago as of today.
Comments
I think the answer lies in this question - what is the key difference between Full join and a Cross Join/cartesian join. Click Here to know the difference
A cross join cannot produce the same outcome as Full Join unless each table has not more than 2 records(<2) in it.
Select * from Employees CROSS JOIN Departments;