16

Is there a way to only SELECT rows with certain data in a column, without using WHERE?

e.g. If I had this:

SELECT * FROM Users
WHERE town = 'Townsville'

is there a way to implement the WHERE clause into the SELECT statement?

something like

SELECT *, town('Townsville') FROM Users

It's a bizarre question but it's one I've been asked by my peers

  • 4
    Ask them why they are asking this. Context is important. – Aaron Bertrand Aug 21 '15 at 15:16
  • @AaronBertrand, MikaelEriksson - Basically it's a little questionnaire about SQL at work, I've come across a question stating "Select all users from the users table that come from Townsville, without using a where clause" And, I didn't know that was possible! Maybe I'm approaching this the wrong way..? – Josh Stevenson Aug 21 '15 at 15:18
  • Also just stating that this questionnaire is in no way linked to my employement status with the company! It's just a bit of fun – Josh Stevenson Aug 21 '15 at 15:21
17

Not sure if this is the kind of crazy thing you were looking for....

Disclaimer: I have absolutely no idea why you would want to use this.

SELECT * 
FROM Users AS u
INNER JOIN (SELECT 'Townsville' town) towns 
  ON towns.town = u.Town;
|improve this answer|||||
17

Data

DECLARE @Example AS table
(
    UserName varchar(30) NULL,
    Town varchar(30) NULL
);

INSERT @Example
    (UserName, Town)
VALUES
    ('Aaron', 'Not Townsville'),
    ('Bob', 'Not Townsville'),
    ('Charles', 'Townsville'),
    ('Charles', 'Townsville'),
    ('Charles', 'Townsville'),
    ('Charles', 'Townsville'),
    ('Dan', 'Townsville'),
    ('Eric', 'Not Townsville');

Alternative solutions

SELECT E.UserName, E.Town
FROM @Example AS E
GROUP BY E.Town, E.UserName
HAVING E.Town = 'Townsville'

-- OR

SELECT E.UserName, 'Townsville' AS Town
FROM @Example AS E
GROUP BY E.UserName
HAVING 1 = MAX(CASE WHEN E.Town = 'Townsville' THEN 1 ELSE 0 END);

-- OR

SELECT E.UserName, E.Town
FROM @Example AS E
INTERSECT
SELECT E.UserName, 'Townsville' AS Town
FROM @Example AS E

Retaining duplicates

-- :)
SELECT E.UserName, E.Town
FROM @Example AS E
CROSS APPLY (VALUES(NEWID())) AS CA (n)
GROUP BY E.Town, E.UserName, CA.n
HAVING E.Town = 'Townsville'

-- Simulating INTERSECT ALL
SELECT
    R.UserName,
    R.Town
FROM 
(
    SELECT 
        E.UserName, 
        E.Town, 
        rn =
            ROW_NUMBER() OVER (
                PARTITION BY E.UserName, E.Town 
                ORDER BY E.UserName, E.Town)
    FROM @Example AS E
    INTERSECT
    SELECT 
        E.UserName, 
        'Townsville', 
        rn = 
        ROW_NUMBER() OVER (
            PARTITION BY E.UserName 
            ORDER BY E.UserName)
    FROM @Example AS E
) AS R;

Output:

╔══════════╦════════════╗
║ UserName ║    Town    ║
╠══════════╬════════════╣
║ Charles  ║ Townsville ║
║ Dan      ║ Townsville ║
╚══════════╩════════════╝

For the final example:

╔══════════╦════════════╗
║ UserName ║    Town    ║
╠══════════╬════════════╣
║ Charles  ║ Townsville ║
║ Charles  ║ Townsville ║
║ Charles  ║ Townsville ║
║ Charles  ║ Townsville ║
║ Dan      ║ Townsville ║
╚══════════╩════════════╝

Try it here: Stack Exchange Data Explorer

  • Very nice! I'm guessing I wouldn't be able to use this in a scenario where I don't have a column containing only unique data such as 'Username'? E.g. if I only had forename, surname, town. – Josh Stevenson Aug 21 '15 at 16:01
  • 3
    @JoshStevenson Correct, though I added a suitably mad way to preserve duplicates as the final example, and then a sensible one. – Paul White 9 Aug 21 '15 at 16:12
  • 1
    For the GROUP BY solutions, you could also add the PK in the group by list (to be 100% sure that the queries return the same number of rows as the WHERE). Assuming of course that there is a PK ;) – ypercubeᵀᴹ Aug 21 '15 at 17:31
15

Another way.

SELECT U.*
FROM  dbo.Users U
CROSS APPLY (SELECT Town INTERSECT SELECT 'Townsville' ) CA;

(Repurposing Paul White's example data)

14

"Just for fun" you can use an order by with top(1) with ties

select top(1) with ties *
from dbo.Users
order by case when town = 'Townsville' then 1 else 2 end;

This will order all rows with Townsville first since the case returns 1 if town = 'Townsville'. All other rows will have a 2 returned by the case.

The with ties clause makes the query return all rows that is a "tie" for the last place in the rows returned. Using top(1) in combination with with ties will then return all rows that has the same value as the first row in expression used in the order by clause.

Note, as Martin Smith pointed out in a comment, it will return all rows if you ask for a town that does not exist in the table.

If you don't fear the XML things of databases you could make use of a predicate in the nodes() function.

Borrowing the setup from Paul White.

select T.X.value('(UserName/text())[1]', 'varchar(30)') as UserName,
       T.X.value('(Town/text())[1]', 'varchar(30)') as Town
from (
     select *
     from @Example
     for xml path('row'), type 
     ) as C(X)
  cross apply C.X.nodes('/row[Town = "Townsville"]') as T(X);

Another version with top and order by that actually work when searching for not existing towns.

select top(
          select sum(case when town = 'Townsville' then 1 end)
          from @Example
          ) *
from @Example
order by case when town = 'Townsville' then 1 else 2 end
7

You have two different things here.

SELECT * FROM Users
WHERE town = 'Townsville'

Will restrict the number of rows you get back to just those where town=Townsville

SELECT *, town('Townsville') FROM Users

Is going to pass the literal Townsville to a function called town. It will not restrict the rows returned by the query and in fact if the function returns anything but a single value you will get an error.

There are other ways to restrict the number of rows you get back from a query. The HAVING clause for example. But it has several other requirements.

SELECT town FROM Users
GROUP BY town
HAVING town = 'Townsville'

Or an INNER JOIN although this one is a bit weird if you don't have a second table.

SELECT * FROM Users
INNER JOIN (SELECT 1 col1) UselessTable
    ON Users.town = 'Townsville'
|improve this answer|||||
5

Here is an example using a common table expression (CTE).

with Town as 
(
    select 'Townsville' as Town
)
select *
  from Users u
  join Town  t on u.Town = t.Town
|improve this answer|||||
5

Well you could do this:

    SELECT A.* 
    FROM Users A
         INNER JOIN Users B ON A.Id = B.Id AND B.town = 'Townsville'

Strictly speaking you're not using the WHERE clause

|improve this answer|||||
5

Here is a idiotic entirely logical way to do it that I don't see yet....

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  -- Our important work should be all the database cares about
GO
BEGIN TRANSACTION

DECLARE @MyTableVar table(<all columns in order from the user table>, oldtown VARCHAR(50));

UPDATE users
SET town = N'Townsville'
OUTPUT 
     inserted.*  -- We don't want to have to type out the columns because that would be too much work
    deleted.town
INTO @MyTableVar;

--Display the result set of the table variable to prevent undesirables from sullying our output by inserting incorrect data even though we should have exclusive access.
SELECT * -- Select everything we want except for the 'oldtown' column because that data was probably wrong anyway
FROM @MyTableVar;

UPDATE u -- We don't want to be bad stewards of our data
SET
    town = oldtown
FROM users u
    INNER JOIN @MyTableVar mtv ON mtv.town = u.town, <Match up all the columns to REALLY ensure we are matching the proper row>

COMMIT TRANSACTION -- Make sure we save our work

I can't imagine why this wasn't the first thing to be suggested. :)

-2
SELECT *, 
    case when town='Townsville' then 'Townsville' 
         else null 
    end as Town
FROM Users

All towns besides Townsville would be null. Problem solved.

Not the answer you're looking for? Browse other questions tagged or ask your own question.