Less Than/Greater Than In SQL

[TWTCommish]

Hi all,

I need some assistance with SQL. I basically need a rundown of how less than/greater than symbols are used within SQL queries, and any advice you might have on related subjects, as I'm working with some queries more advanced that ever before (for me, that is), and could very much use the help!

Thanks.

[MattR]

I assume you mean performance issues?

they're used in the WHERE clauses:
e.g.
WHERE column1 > column2
or
WHERE column2 >= 12

Pretty simple stuff.

There are some performance implications on > vs. >=, though.

This query, with an index on int_col:

SELECT * FROM table WHERE int_col > 3

uses the index to find the first value where int_col equals 3, and then scans forward to find the first value that is greater than 3. If there are many rows where int_col equals 3, the server has to scan many pages to find the first row where int_col is greater than 3.

It is probably much more efficient to write the query like this:
SELECT * FROM table WHERE int_col >= 4

In the same vein, if you want to find non-zero values do NOT do WHERE int_col <> 0, do WHERE int_col >= 1 (if you know it's not negative).

[TWTCommish]

What about more than one symbol, would it work like this?

SELECT * FROM table WHERE int >= 5 AND int <= 10

[MattR]

Originally posted by TWTCommish
What about more than one symbol, would it work like this?

SELECT * FROM table WHERE int >= 5 AND int <= 10

Yep, or you can use BETWEEN,
WHERE int BETWEEN( 5, 10 )

I think the syntax in MySQL is a bit different, but it's close to that. You can have any number of < > in queries.

WHERE int > 10 or sometotherval = 3 and joe < 10

[TWTCommish]

Thanks Matt, this should prove to be enough to get me rolling.