SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
Thread: Less Than/Greater Than In SQL
-
Apr 6, 2001, 07:08 #1
- Join Date
- Aug 1999
- Location
- Pittsburgh, PA, USA
- Posts
- 3,910
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Apr 6, 2001, 07:41 #2
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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).Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Apr 6, 2001, 07:49 #3
- Join Date
- Aug 1999
- Location
- Pittsburgh, PA, USA
- Posts
- 3,910
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
What about more than one symbol, would it work like this?
SELECT * FROM table WHERE int >= 5 AND int <= 10
-
Apr 6, 2001, 07:58 #4
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally posted by TWTCommish
What about more than one symbol, would it work like this?
SELECT * FROM table WHERE int >= 5 AND int <= 10
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 < 10Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Apr 6, 2001, 10:27 #5
- Join Date
- Aug 1999
- Location
- Pittsburgh, PA, USA
- Posts
- 3,910
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks Matt, this should prove to be enough to get me rolling.
Bookmarks