CODEWITHSUNDEEP
mysqlsql
diikuun
diikuun

Reputation: 37

SQL Cross Join practical use

I'm currently learning about SQL syntax using MySQL server and I have encountered this join function called a cross join. I am just curious about this cross join practical use. So from your experience, is there any cases that need the cross-join function? Because so far I can't seem to understand why would anybody wants to connect all values using cross join. Is there any example case of the time when cross join can come in handy?

Upvotes: 1

Views: 367

Answers (1)

Caius Jard
Caius Jard

Reputation: 74615

Sure. Say I want to write a report that has a list of every shape and color, and linked to that a number of people who say that it's their favorite shape and color

Shapes
------
Triangle
Square


Colors
-----
Red
Green
Blue

People
-----
John, Blue, Triangle
Mary, Blue, Triangle
Steve, Red, Square


SELECT 
  s.Shape,
  c.Color,
  Count(*)
FROM
  Shapes s 
  CROSS JOIN Colors c 
  LEFT JOIN People p ON s.Shape = p.Shape and c.Color = p.Color
GROUP BY
  s.Shape,
  c.Color

The results have every shape and color combo even though my people data doesn't

Red,   Triangle, 0
Green, Triangle, 0
Blue,  Triangle, 2
Red,   Square,   1
Green, Square,   0
Blue,  Square,   0

We can use cross join when we want to create combinations, especially if we don't have those combinations anywhere in our actual data

Upvotes: 3

Related Questions