PL SQL - Multiple Column Equality

Question

I am trying to evaluate a few columns in order to save a few keystrokes (assuming that at the moment the time and effort of the search has long been denying any "benefit" that I have ever received), and not several different comparisons.

Basically, I have:

WHERE column1 = column2 AND column2 = column3 

I want to:

 WHERE column1 = column2 = column3 

I found this other tangent article: Oracle SQL syntax - check multiple columns for IS NOT NULL

Answer 1

Using:

 x=all(y,z) 

instead

 x=y and y=z 

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

If the column names are larger, then this gives a big savings:

This is a length of 35 characters:

 column1=column2 AND column2=column3 

so far this one is only 28

  column1=ALL(column2,column3) 

But for this (95 characters):

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

you get 43/95 = almost 50% savings

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

---

Operator

ALL is 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 showing 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); XYZ ---------- ---------- ---------- 1 1 1 

Answer 2

One possible trick is to use the least and greatest - if the largest and smallest values ​​in the list of values ​​are equal, this means that all values ​​are equal:

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

I'm not sure if it saves any keystrokes in a three-column list, but if you have many columns, this can save a few characters. Note that this solution implicitly assumes that none of the values ​​is null , but also your original solution, so it should be ok.