What happened when you use table1.* <> table2.*?-CodePudding

Imagine 2 table with the same structure but that might have some rows not equal for the same primary key.

What really happen when using a where clause like this : " where table1.* <> table2.* " ?

I "used" it in PostgreSQL but I'm interested for other's languages behavior with this weird thing.

CodePudding user response：

This statement is comparing, every column together of the first table to every column together of the second table. It is the same as writing the composite type explicitly, which would be required if the columns are not in the same order in both tables.

(t1.id, t1.col1, t1.col2) <> (t2.id, t2.col2, t2.col2)

or even more verbose

t1.id <> t2.id
OR t1.col1 <> t2.col1
OR t1.col2 <> t2.col2

But you may want to use IS DISTINCT FROM instead of <> to consider null vs not null as being different/not equal.

CodePudding user response：

In postgres t1.* <> t2.* in this context is expanded to be:

(t1.c1, t1.c2, ..., t1.cn) <> (t2.c1, t2.c2, ..., t2.cn)

which is the same as:

(t1.c1 <> t2.c1) OR (t1.c2 <> t2.c2) OR ...

I think the expansion is a postgres extension to the standard, tuple comparision exists in several other DBMS. You can read about it at https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS

The number of columns is required to be the same when comparing tuples, but I discovered something peculiar when trying your example:

create table ta (a1 int);
create table tb (b1 int, y int);
select * from ta cross join tb where ta.* <> tb.*

The last select succeds, despite the tuples having different number of columns. Adding some rows change that:

insert into ta values (1),(2);
insert into tb values (2,1),(3,4);
select * from ta cross join tb where ta.* <> tb.* 
ERROR:  cannot compare record types with different numbers of columns

so it appears as this is not checked when the statement is prepared. Expanding the tuple manually yields an ERROR even with empty tables:

select * from ta cross join tb where (ta.a1, ta.a1) <> (tb.b1, y, y); 
ERROR:  unequal number of entries in row expressions

Fiddle