SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard TWTCommish's Avatar
    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.

  2. #2
    Database Jedi MattR's Avatar
    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).

  3. #3
    SitePoint Wizard TWTCommish's Avatar
    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

  4. #4
    Database Jedi MattR's Avatar
    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
    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

  5. #5
    SitePoint Wizard TWTCommish's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •