Join on 1=1 versus cross join

Question

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 1

One thing to note is a cross join will result in an empty table if one of the tables is empty. If one of your tables might be empty and you still want records, you may want an outer join (e.g. left, right, or full) on 1=1.

Answer 2

The reason 1=1 exists is to make it easier to create dynamic sql statements by concatenating strings together (with the usual safeguards like parameterization, of course).

Having a predefined WHERE clause with 1=1 in it allows additional WHERE conditions to be added to the SQL without having to check for the existence of a WHERE clause first, and the SQL engine will generally optimize out the 1=1 so there's no performance difference.

In any other context, 1=1 is generally harmless, but not particularly useful.

Answer 3

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

See Also: Correct way to select from two tables in SQL Server with no common field to join on

Answer 4

Based solely on my experience after writing huge amounts of sql. inner join on 1=1 is much faster than simply cross join.