Home > SQL Challenge > SQL – Cross Join

SQL – Cross Join

A cross join (or Cartesian Product join) will return a result table where each row from the first table is combined with each row from the second table. The number of rows in the result table is the product of the number of rows in each table. If the tables involved are large, this join can take a very long time.

A cross join can be specified in two ways: using the JOIN syntax or by listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria.

SELECT * FROM A CROSS JOIN B

SELECT * FROM A, B

I prefer the first syntaxt because itwill be obvious to other developers who might need to modify my code in teh future.

This is an example where I used CROSS JOIN to combine every date in the month with every transaction types:

SELECT A.DT
 ,  B.REFERRAL_SOURCE_GROUPING
 ,  C.TYPE_ORDER
 ,  C.REFERRAL_TYPE
 INTO #REFERRAL_TYPE
 FROM ( SELECT DISTINCT DT
    FROM #REF ) A
 CROSS JOIN
 ( SELECT ‘1) Early Intervention Referrals’  AS REFERRAL_SOURCE_GROUPING UNION ALL
  SELECT ‘2) Collections Referrals’ AS REFERRAL_SOURCE_GROUPING UNION ALL
  SELECT ‘3) HES Referrals’ AS REFERRAL_SOURCE_GROUPING ) B
 CROSS JOIN
 ( SELECT 1 AS TYPE_ORDER, ‘Non-HEART Ref’ AS REFERRAL_TYPE UNION ALL
  SELECT 2 AS TYPE_ORDER, ‘HEART Ref’ AS REFERRAL_TYPE UNION ALL
  SELECT 3 AS TYPE_ORDER, ‘HEART Recmdtn’ AS REFERRAL_TYPE UNION ALL
  SELECT 4 AS TYPE_ORDER, ‘WFHM’ AS REFERRAL_TYPE ) C

Categories: SQL Challenge
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: