Skip to main content
Share Your Experience: Take the 2024 Developer Survey
47 votes

NOT (a=1 AND b=1) vs (a<>1 AND b<>1)

They are not equivalent. NOT (a=1 AND b=1) is equivalent with: (NOT a=1 OR NOT b=1) <=> (a<>1 OR b<>1) This equivalence is known as De Morgan's Law. See for example: https://en....
Lennart - Slava Ukraini's user avatar
22 votes
Accepted

Conditional string concatenation in PostgreSQL

The function concat_ws() does exactly what you want. The first parameter is used as glue between the rest. Nulls are ignored: select concat_ws('//', owner_addr1, owner_addr2, owner_addr3) Test: red=...
ypercubeᵀᴹ's user avatar
16 votes

Invert a Boolean expression which can return UNKNOWN

You could enclose the condition in a CASE expression that returns a binary result, for instance 1 or 0: SELECT ... FROM ... WHERE CASE WHEN someColumn = someValue THEN 1 ELSE 0 END = 1 ; ...
Andriy M's user avatar
  • 23k
10 votes

Invert a Boolean expression which can return UNKNOWN

The first thought that occurs to me: DECLARE @T AS table (c1 integer NULL); INSERT @T (c1) VALUES (1), (NULL), (2); -- Original expression c1 = 1 SELECT T.c1 FROM @T AS T WHERE c1 = 1; Returns: --...
Paul White's user avatar
  • 85.5k
9 votes

NOT (a=1 AND b=1) vs (a<>1 AND b<>1)

Your first example is saying: Return all rows except where both a = 1 AND b = 1 Your second example is saying: Return all rows except where either a = 1 OR b = 1 For the second query to return ...
Mark Sinkinson's user avatar
6 votes

How to use a conditional ORDER BY in MySQL?

As Lennart explains, a CASE expression in MySQL can only produce a single value, not two or more. I think this would suite your problem: ORDER BY CASE WHEN favorited = 1 THEN 0 ELSE 1 END, ...
ypercubeᵀᴹ's user avatar
6 votes
Accepted

Does the "don't use errors for flow control" axiom apply to postgres?

Can't tell, from the top of my head, the exact sequence of events in case of an exception. But I can tell you that it is comparatively expensive and should be avoided unless needed. An exception (...
Erwin Brandstetter's user avatar
5 votes
Accepted

How do I select arrays that are not empty?

The reason seems to be that empty strings in that column have array dimensions [1:0]. Should normally be NULL. See: SELECT lexemes, array_dims(lexemes) FROM ts_debug('a title'); lexemes | ...
Erwin Brandstetter's user avatar
5 votes
Accepted

How to use a conditional ORDER BY in MySQL?

A case expression returns an atomic value so you can't return: favoritedDate, dateUpload I think this will do what you want: ORDER BY CASE favorited WHEN 1 THEN favoritedDate ...
Lennart - Slava Ukraini's user avatar
4 votes
Accepted

How to use IN clause in sql server so that it selects everything if nothing is provided for its list argument?

So how do I get ALL rows if nothing is passed into IN clause? You have to use dynamic sql. e.g. Below is a simple example to get you started. set nocount on declare @sqltext nvarchar(max) = N'' ...
Kin Shah's user avatar
  • 62.3k
4 votes
Accepted

If a record does not exist set a value

First, the query you have is not valid. I assume you omitted the GROUP BY a.email when posting the question. You also alias foo as c but then you reference to i.total. Now for the missing results, it'...
ypercubeᵀᴹ's user avatar
4 votes
Accepted

Where Clause for Results Appearing after Occurence

it seems you are looking for something like this: with cte as ( select * ,row_number() over(order by col_b) as rn from dummy ) select * from cte where rn > (select rn from cte ...
NikitaSerbskiy's user avatar
3 votes

Invert a Boolean expression which can return UNKNOWN

If you don't mind rewriting the sub-expressions up front, you can use COALESCE: SELECT * FROM myTable WHERE NOT (COALESCE(myField, 'notSomeValue') = 'someValue') You must make sure that '...
jpmc26's user avatar
  • 1,602
3 votes

How to use IN clause in sql server so that it selects everything if nothing is provided for its list argument?

In SQL 2016 and later, you can use JSON for this without dynamic SQL. You can have a static query like: select * from SomeTable where @values is null or SomeCol in ( select value from openjson(@...
David Browne - Microsoft's user avatar
3 votes
Accepted

SSAS Calculated Member Conditional Count

Assuming you already have designed a measure Total that counts the accidents a dimension Severity that describes the severity of the accident you can define a calculated member such as: ([...
irimias's user avatar
  • 1,921
3 votes
Accepted

In what direction SQL Server WHERE clause conditions are checked?

There's not such thing as a order of execution in the WHERE clause. You are thinking in the imperative and procedural paradigm like most development languages (Java, C++, VB, JavaScript). SQL is (...
jean's user avatar
  • 495
3 votes

In what direction SQL Server WHERE clause conditions are checked?

I don't think there are any guarantees for evaluation order. The optimizer will try to find the most efficient way to execute the query using available information and each invocation of the query may ...
Scott Hodgin - Retired's user avatar
3 votes

Where Clause for Results Appearing after Occurence

Easily. WITH cte AS ( SELECT col_a, col_b, col_c, test, ROW_NUMBER() OVER (ORDER BY col_b) rn FROM dummy ) SELECT col_a, col_b, col_c, test FROM cte WHERE rn > ( ...
Akina's user avatar
  • 20k
3 votes

A flag (indicator) that shows for each row which conditions was used to select it

If your conditions are mutually exclusive (you you only care about matching the first of an order you control) then you could get away with a single column: SELECT *, CASE WHEN name = '...
Caius Jard's user avatar
2 votes

In where clause, does MySQL continue checking conditions if first condition before AND falsy

I did some experimenting. Here's my guess: WHERE (constant-expression-evaluating-to-FALSE) AND (SELECT ...) It will evaluate the constant and stop. WHERE (col ... ) -- some test that can't use ...
Rick James's user avatar
  • 78.3k
2 votes
Accepted

IS NOT DISTINCT FROM vs row-wise equality with =

This behavior is documented under Composite Type Comparison, The SQL specification requires row-wise comparison to return NULL if the result depends on comparing two NULL values or a NULL and a non-...
Evan Carroll's user avatar
  • 63.5k
2 votes
Accepted

MySql sort conditional based on column values

I think this would work - although it's rather obfuscated: ORDER BY price = 0, LEAST(stock, 100) DESC, price ; More clearly: ORDER BY CASE WHEN price > 0 THEN 1 ELSE 2 END, ...
ypercubeᵀᴹ's user avatar
2 votes
Accepted

Conditional where for postgres stored procedure

You can use Boolean expressions, that return true, if a parameter is null or empty, otherwise the result of a LIKE operation. ... SELECT DISTINCT t.serialnumber as sn, t.productid as ...
sticky bit's user avatar
  • 4,864
2 votes

Conditional string concatenation in PostgreSQL

Probably most obvious to use a CASE statement. There are 4 cases: owner_addr2 IS NULL AND owner_addr3 IS NULL => '' owner_addr2 IS NULL AND owner_addr3 IS NOT NULL => '//' || owner_addr3 ...
Lennart - Slava Ukraini's user avatar
2 votes

Invert a Boolean expression which can return UNKNOWN

There's the built-in EXCEPT set operator which, effectively, removes a second query's results from the first query's. select * from table except select * from table where <really complex ...
Michael Green's user avatar
2 votes
Accepted

Updating whole table once, criteria : column_name and each value

This is a very simple query. You can use a CASE expression, one for each column that needs updating: UPDATE table_name SET other1 = CASE WHEN other1 <= 0 THEN other1 -- no change ...
ypercubeᵀᴹ's user avatar
2 votes

If a record does not exist set a value

Use a subquery to calculate total: select a.email, coalesce((select sum(c.total) from accounts b inner join foo c on b.id = c.account ...
McNets's user avatar
  • 23.8k
2 votes
Accepted

If true then insert event

Try changing your OR to AND - IF SUSER_SNAME() not like '%someuser%' AND CURRENT_USER NOT like '%anotheruser%'.
Scott Hodgin - Retired's user avatar
2 votes

Matching multiple rows in a MySQL database

This is usually done using conditional aggregation plus some applied logic: select order_id, case max(case when product_type = 'A' then 1 else 0 end) + max(case when product_type = 'B' ...
dnoeth's user avatar
  • 4,196
2 votes

How to use IN clause in sql server so that it selects everything if nothing is provided for its list argument?

You should change your program to not send through the where clause rather than using dynamic SQL or JSON or some other technique. Add an if statement when building your code to control addding the ...
Greg's user avatar
  • 3,252

Only top scored, non community-wiki answers of a minimum length are eligible