Alternative to WHERE clause [on hold]

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;

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

Another way.

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

(Repurposing Paul White's example data)

"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

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'

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. :)

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

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

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

All towns besides Townsville would be null. Problem solved.