1

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?

NomardicRoku
  • 81
  • 1
  • 5
  • Without a WHERE-condition the result of your query will always be a Cartesian Product, the same as an explicit `Select * from Employees CROSS JOIN Departments;` – dnoeth Jul 26 '19 at 15:52
  • A similar discussion is here (https://stackoverflow.com/questions/3228871/sql-server-what-is-the-difference-between-cross-join-and-full-outer-join). Cartesian join is cartesian product (please note the use of product) of tables. So if table A has X number of rows and table B has Y number of rows, in total it will have X * Y number of rows (all possible combinations). Full Outer join, on the other hand is a combination of Left Outer join and Right Outer join. – Amit Jul 26 '19 at 15:57

2 Answers2

2

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.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
0

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.

James NG
  • 1