Join on 1=1 versus cross join

I am joining a large table in postgresql to a table with 1 row in it. Yes, I know I could just take the values from this single row table and put them in my query written out, but there's 210 columns.

So my question is this: should I join up the single row table to everything using a cross join or using a regular join on a tautology (1 = 1 or something). Is either of those ways bound to be slower?

Or is there a third faster way?

Answer

January 9, 2020

KyleMit

They should perform and behave the same - the difference is in developer semantics

Here's a sample demo:

-- Setup
DECLARE @Codeset TABLE (id INT, [name] VARCHAR(50));
DECLARE @Student TABLE (id INT, [name] VARCHAR(50), age INT);

INSERT INTO @Codeset  
VALUES (1, 'HomeRoom');

INSERT INTO @Student  
VALUES (1, 'Jolly', 20), 
       (2, 'Sally', 22);

Let's say we want back every row from @Student and also to bring back the name value from @Codeset:

| id | name  | age | name     |
|----|-------|-----|----------|
| 1  | Jolly | 20  | HomeRoom |
| 2  | Sally | 22  | HomeRoom |

Any of the following syntaxes will accomplish this when either table has 0, 1, or 1+ records:

-- INNER JOIN 1=1
SELECT s.id, s.[name], s.age, c.[name]
FROM @Student s
INNER JOIN @Codeset c ON 1=1

-- Multiple From tables
SELECT s.id, s.[name], s.age, c.[name]
FROM @Student s,
     @Codeset c

-- CROSS JOIN
SELECT s.id, s.[name], s.age, c.[name]
FROM @Student s
CROSS JOIN @Codeset c

In this situation, you should prefer CROSS JOIN to be as explicit as possible as to your intentions to build a single select statement from multiple unrelated tables

Note: CROSS JOIN will output the same as INNER JOIN ON 1=1. Both produce the Cartesian product of all rows in both rowsets, so if one table is empty, the results will be as well.

If you need to guarantee results when @Students has records, but @Codeset might be empty, you will need to use LEFT JOIN ON 1=1