How many ways can one rewrite a very simple query with MSSQL
- In Programming and especially with SQL, there is more than one way to write a simple query.
- Contrarily to the usual way, the data used is at the end.
- This will become an ongoing project.
- If you have any suggestion, email me syv@sqlhacks.com with your name, and I will add it with your name, title and company. You can also send some explanations.
- Please note that using date and times will create even many more variations, because you can take dates apart in many more ways with datediff, dateadd and datepart.
Simplest query: How to get the data for employee '000110'
SELECT emp_short, sales_date, sales_qty
FROM sales
WHERE emp_short = '000110'
ORDER BY sales_date;
go
FROM sales
WHERE emp_short = '000110'
ORDER BY sales_date;
go
emp_short sales_date sales_qty ---------- ----------------------- --------------------------------------- 000110 2004-03-06 14:01:01.000 5.00 000110 2005-11-06 10:01:01.000 9.00 000110 2006-02-06 13:01:01.000 6.00 000110 2006-03-06 14:01:01.000 5.00 000110 2006-06-06 17:01:01.000 2.00 000110 2006-10-06 10:01:01.000 10.00 000110 2006-10-06 10:01:01.000 3.00 000110 2007-05-09 14:00:00.000 1.25 (8 row(s) affected)
Alternatives
Syv Ritch db Networks: 7-Jul-2007
SELECT emp_short, sales_date, sales_qty
FROM sales
WHERE emp_short = '000110'
ORDER BY sales_date;
go
FROM sales
WHERE emp_short = '000110'
ORDER BY sales_date;
go
Syv Ritch db Networks: 7-Jul-2007
SELECT emp_short, sales_date, sales_qty
FROM sales
WHERE NOT emp_short <> '000110'
ORDER BY sales_date;
go
FROM sales
WHERE NOT emp_short <> '000110'
ORDER BY sales_date;
go
Notice the double negative.
Syv Ritch db Networks: 7-Jul-2007
SELECT emp_short, sales_date, sales_qty
FROM sales
WHERE emp_short in ('000110')
ORDER BY sales_date;
go
FROM sales
WHERE emp_short in ('000110')
ORDER BY sales_date;
go
Syv Ritch db Networks: 7-Jul-2007
SELECT emp_short, sales_date, sales_qty
FROM sales
WHERE not emp_short not in ('000110')
ORDER BY sales_date;
go
FROM sales
WHERE not emp_short not in ('000110')
ORDER BY sales_date;
go
Another variation of the double negative.
Syv Ritch db Networks: 7-Jul-2007
SELECT emp_short, sales_date, sales_qty
FROM sales
WHERE emp_short like '000110'
ORDER BY sales_date;
go
FROM sales
WHERE emp_short like '000110'
ORDER BY sales_date;
go
Syv Ritch db Networks: 7-Jul-2007
SELECT emp_short, sales_date, sales_qty
FROM sales
WHERE emp_short between '000110' and '000110'
ORDER BY sales_date;
go
FROM sales
WHERE emp_short between '000110' and '000110'
ORDER BY sales_date;
go
Between can be the same value.
Syv Ritch db Networks: 7-Jul-2007
SELECT emp_short, sales_date, sales_qty
FROM sales
WHERE not emp_short not between '000110' and '000110'
ORDER BY sales_date;
go
FROM sales
WHERE not emp_short not between '000110' and '000110'
ORDER BY sales_date;
go
Double negative for the between.
Syv Ritch db Networks: 7-Jul-2007
SELECT emp_short, sales_date, sales_qty
FROM sales
WHERE emp_short = some (
SELECT abc.emp_short
FROM sales abc
WHERE emp_short = '000110'
)
ORDER BY sales_date;
go
FROM sales
WHERE emp_short = some (
SELECT abc.emp_short
FROM sales abc
WHERE emp_short = '000110'
)
ORDER BY sales_date;
go
Subqueries, here we come. There will be many more variations of the subqueries at future times.
Syv Ritch db Networks: 7-Jul-2007
SELECT emp_short, sales_date, sales_qty
FROM sales
WHERE emp_short = any (
SELECT abc.emp_short
FROM sales abc
WHERE emp_short = '000110'
)
ORDER BY sales_date;
go
FROM sales
WHERE emp_short = any (
SELECT abc.emp_short
FROM sales abc
WHERE emp_short = '000110'
)
ORDER BY sales_date;
go
Syv Ritch db Networks: 7-Jul-2007
SELECT emp_short, sales_date, sales_qty
FROM sales
WHERE emp_short = all (
SELECT abc.emp_short
FROM sales abc
WHERE emp_short = '000110'
)
ORDER BY sales_date;
go
FROM sales
WHERE emp_short = all (
SELECT abc.emp_short
FROM sales abc
WHERE emp_short = '000110'
)
ORDER BY sales_date;
go
From: Denis The SQL Menace: 9-Jul-2007
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx
SELECT emp_short, sales_date, sales_qty
FROM sales
WHERE emp_short != '000110'
ORDER BY sales_date;
go
FROM sales
WHERE emp_short != '000110'
ORDER BY sales_date;
go
From: Denis The SQL Menace: 9-Jul-2007
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx
SELECT emp_short, sales_date, sales_qty
FROM sales
WHERE REVERSE(emp_short) = '011000'
ORDER BY sales_date;
go
FROM sales
WHERE REVERSE(emp_short) = '011000'
ORDER BY sales_date;
go
From: Denis The SQL Menace: 9-Jul-2007
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx
SELECT emp_short, sales_date, sales_qty
FROM sales
WHERE CONVERT(VARBINARY,emp_short) = 0x303030313130
ORDER BY sales_date;
go
FROM sales
WHERE CONVERT(VARBINARY,emp_short) = 0x303030313130
ORDER BY sales_date;
go
Data used
USE sql911;
go
SELECT emp_short, sales_date, sales_qty
FROM sales
ORDER BY emp_short, sales_date;
go
go
SELECT emp_short, sales_date, sales_qty
FROM sales
ORDER BY emp_short, sales_date;
go
emp_short sales_date sales_qty ---------- ----------------------- --------------------------------------- 000010 2005-04-06 15:01:01.000 4.00 000010 2005-11-06 10:01:01.000 9.00 000010 2005-12-06 11:01:01.000 8.00 000010 2006-04-06 15:01:01.000 4.00 000010 2006-05-06 16:01:01.000 3.00 000010 2006-07-06 19:01:01.000 1.00 000010 2007-04-06 00:00:00.000 4.00 000010 2007-05-03 09:00:00.000 3.00 000010 2007-05-05 04:00:00.000 1.00 000010 2007-05-07 11:00:00.000 2.00 000010 2007-05-08 05:00:00.000 1.00 000010 2007-05-08 07:00:00.000 2.00 000010 2007-05-08 11:00:00.000 3.00 000010 2007-05-08 15:00:00.000 1.50 000010 2007-06-06 17:01:01.000 2.00 000030 2005-10-06 10:01:01.000 10.00 000030 2006-01-06 12:01:01.000 7.00 000030 2006-04-06 15:01:01.000 4.00 000030 2006-06-06 17:01:01.000 2.00 000030 2006-10-06 10:01:01.000 10.00 000030 2007-05-01 00:00:00.000 5.00 000030 2007-05-07 11:30:00.000 2.00 000030 2007-05-08 13:30:00.000 1.00 000030 2007-11-06 10:01:01.000 9.00 000110 2004-03-06 14:01:01.000 5.00 000110 2005-11-06 10:01:01.000 9.00 000110 2006-02-06 13:01:01.000 6.00 000110 2006-03-06 14:01:01.000 5.00 000110 2006-06-06 17:01:01.000 2.00 000110 2006-10-06 10:01:01.000 10.00 000110 2006-10-06 10:01:01.000 3.00 000110 2007-05-09 14:00:00.000 1.25 000190 2005-03-06 14:01:01.000 5.00 000190 2005-05-06 16:01:01.000 3.00 000190 2005-12-06 11:01:01.000 8.00 000190 2006-02-06 13:01:01.000 6.00 000190 2006-06-06 17:01:01.000 2.00 000190 2006-07-06 00:00:00.000 1.00 000190 2006-07-06 19:01:01.000 1.00 000190 2006-10-06 10:01:01.000 10.00 000190 2006-10-06 10:01:01.000 10.00 000190 2007-05-10 14:00:00.000 5.00 000190 2007-05-11 14:15:00.000 4.00 000190 2007-05-12 15:00:00.000 2.00 000190 2007-05-13 16:00:00.000 1.00 000190 2007-07-06 00:00:00.000 1.00 000310 2004-10-06 10:01:01.000 10.00 000310 2005-10-06 10:01:01.000 10.00 000310 2006-01-06 12:01:01.000 7.00 000310 2006-03-06 14:01:01.000 5.00 000310 2006-07-06 19:01:01.000 1.00 000310 2006-10-06 10:01:01.000 10.00 000310 2006-10-06 10:01:01.000 10.00 000310 2007-05-07 00:00:00.000 2.00 000310 2007-05-08 05:00:00.000 1.00 000310 2007-05-11 13:00:00.000 3.50 000310 2007-05-14 11:00:00.000 3.00 (57 row(s) affected)
