SQL If Value Exists then return that otherwise return whole table-CodePudding

Note: This is an over simplified version of what I am trying to do.

Let's say I have a table called USERS that has FIRSTNAME, LASTNAME. Contents are:

JOHN   SMITH
DAVID  BECKHAM
ELON   MUSK

I want to query this table so that if a value exists it just returns that value, otherwise it returns the whole table. Example:

SELECT *
FROM USERS
WHERE FIRSTNAME = 'JOHN'

This would return

JOHN   SMITH

However if the query was

SELECT *
FROM USERS
WHERE FIRSTNAME = 'JANE'

Then the return would be

JOHN   SMITH
DAVID  BECKHAM
ELON   MUSK

This is being done in SQL Server but there is potential for Oracle too in case it is different.

CodePudding user response：

Here is a sample which might work in Oracle or SQL-server, if your RDBMS support CTE

We can try to use UNION ALL and NOT EXISTS to make it.

Filter FIRSTNAME in CTE if the first result exists value which means you filter a FIRSTNAME then you will only get the record (because NOT EXISTS will not let the second result set work).

;WITH CTE AS (
  SELECT *
  FROM USERS
  WHERE FIRSTNAME = 'JANE'
)
SELECT FIRSTNAME,LASTNAME
FROM CTE 
UNION ALL
SELECT FIRSTNAME,LASTNAME
FROM USERS 
WHERE NOT EXISTS (
   SELECT 1
   FROM CTE
)

sqlfiddle

CodePudding user response：

You could do something like this - perhaps with slight variations in syntax between the SQL dialects. Find the occurrence of 'John' in the order by clause of the analytic function in the subquery - that's where you should have your bind variable instead.

select first_name, last_name
from   (
         select first_name, last_name,
                dense_rank() over (order by case first_name
                                     when 'John' then 1 end) as rn
         from   users
       )
where  rn = 1
;

How this works: The case expression assigns the value 1 when the first name is John (or whatever the input is), and null otherwise. So, either some rows are assigned 1 and others null, or else all rows are assigned null. The dense_rank analytic function will assign 1 to the rows with a non-null value and 2 to the rows assigned null, or else it will assign 1 to all rows, if all rows are assigned null in the case expression. Then the outer query selects only the rows where the dense rank is 1; these are all the "John" rows, if there are any, but otherwise it selects all rows.

CodePudding user response：

You want to select a row when the name matches or there is no match for that name in the table. Translated almost word by word into SQL:

SELECT *
FROM users
WHERE firstname = 'JOHN'
OR NOT EXISTS (SELECT * FROM users WHERE firstname = 'JOHN');