Question

In SELECT statements, what are the comparison operands? Provide a SELECT example

Answer 1

Note: Done accordingly. Please comment for any problem. Please Uprate. Thanks.

Comparison operators that compare operands in Select statement are:

• = Equal
  • Eg. Select * from student where age = 25;

• <> Not Equal
  • Eg. Select * from employee where salary <> 25000;

• !=            Not Equal
  • Eg. Select * from employee where salary != 25000;

• > Greater Than
  • Eg. Select * from doctors where age > 60;

• >=           Greater Than or Equal
  • Eg. Select * from doctors where age >= 60;

• <             Less Than
  • Eg. Select * from doctors where age < 60;

• <=           Less Than or Equal
  • Eg. Select * from doctors where age <= 60;

• IN ( )      Matches a value in a list
  • SELECT * FROM `members` WHERE `membership_number` IN (1,2,3);

• NOT       Negates a condition
  • SELECT * FROM `members` WHERE `membership_number` IN (1,2,3);

• BETWEEN            Within a range (inclusive)
  • SELECT * FROM Products
    WHERE Price BETWEEN 10 AND 20;

• IS NULL NULL value
  • SELECT Id, CompanyName, Phone, Fax FROM Supplier WHERE Fax IS NULL

• IS NOT NULL       Non-NULL value
  • SELECT * FROM Supplier WHERE Fax IS NULL

• LIKE        Pattern matching with % and _
  • % - The percent sign represents zero, one, or multiple characters
  • - The underscore represents a single character
  • Eg. SELECT * FROM Customers
    WHERE CustomerName LIKE '_K%';
  • selects all customers with a CustomerName that have "K" in the second position

• EXISTS   Condition is met if subquery returns at least one row
  • SELECT SupplierName
    FROM Suppliers
    WHERE EXISTS (SELECT ProductName FROM Products WHERE Price = 22);