PL SQL - Multiple column equality

Question

I'm trying to evaluate multiple columns to save myself a few keystrokes (granted, at this point, the time and effort of the search has long since negated any "benefit" I would ever receive) rather than multiple different compares.

Basically, I have:

WHERE column1 = column2
AND column2 = column3

I want:

WHERE column1 = column2 = column3

I found this other article, that was tangentially related: Oracle SQL Syntax - Check multiple columns for IS NOT NULL

Answer 1

Use:

x=all(y,z)

instead of

x=y and y=z

The above saves 1 keystroke (1/11 = 9% - not much).

If column names are longer, then it gives bigger savings:

This is 35 characters long:

column1=column2 AND column2=column3

while this one only 28

 column1=ALL(column2,column3)

But for this one (95 characters):

column1=column2 AND column2=column3 AND column3=column4 
AND column4=column5 AND column5=column6

you will get 43/95 = almost 50% savings

column1=all(column2,column3,column4,column5,column6)

---

ALL operator is a part of ANSII SQL, it is supported by most databases (Mysql, Postgresql, SQLServer etc.
http://www.w3resource.com/sql/special-operators/sql_all.php

---

A simple test case that shows how it works:

create table t( x int, y int, z int );

insert all 
into t values( 1,1,1)
into t values(1,2,2)
into t values(1,1,2)
into t values(1,2,1)
select 1 from dual;

select *
from t
where x = all(y,z);

        X          Y          Z
---------- ---------- ----------
        1          1          1 

Answer 2

One possible trick is to utilize the least and greatest functions - if the largest and the smallest values of a list of values are equal, it must mean all the values are equal:

LEAST(col1, col2, col3) = GREATEST(col1, col2, col3)

I'm not sure it saves any keystrokes on a three column list, but if you have many columns, it could save some characters. Note that this solution implicitly assumes that none of the values are null, but so does your original solution, so it should be OK.