Thursday, 15 March 2012

Menggunakan WHERE Clause dalam SQL lebih dari satu

Mudah-mudahan bermanfaat bagi yang sedang melakukan coding. Artikel dalam bahasa inggris, silahkan diterjemahkan sendiri, hitung-hitung sambil belajar, kalau ada pertanyaan monggo manfaatkan komentar di bawah artikel ini...:D
In our introduction to the SQL SELECT statement we saw how to select specific columns from a table. In this article we will see how the WHERE clause can be used to select specific rows from the table that match certain specified criteria or conditions.
The WHERE clause is an optional clause in the following SQL statements:
  • SELECT statments
  • DELETE statements
  • UPDATE statements
This makes the WHERE clause a major foundation to build upon when learning SQL as it is so frequently used.

Comparison Operators

SQL uses comparison operators in the WHERE clause to set the criteria that the rows must meet. These comparison operators compare two values against each other, e.g. is x greater than y. The comparison operators return either True or False. ( e.g. x is either greater than y or it isn't. There can be no other result).
The comparison operators are listed in the table below.

Comparison Operator Meaning
= Equal to
<> Not Equal to
!= Not Equal to
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
!< Not Less than
!> Not greater than

 

The WHERE clause compares the value in a specified column against a specified value and only returns rows where the comparison evaluates to True. The following code demonstrates this by only returning rows from the customer table where the value in the NumberOfOrders column is greater than 5: 


SELECT Forename, Surname, CustomerID 
FROM Customers
WHERE NumberOfOrders > 5
 
 
These comparison operators can be used on other non numeric data types such as string data types, DateTime data types and Boolean (yes/no) data types:


SELECT *
FROM Customers
WHERE CountryCode = 'UK'

SELECT *
FROM Customers
WHERE LastUpdated <= '01/05/2007'

SELECT *
FROM Customers
WHERE Expired <> True
 
 

Logical Operators

ogical operators combine with the comparison operators outlined above to give the WHERE clause the ability to handle multiple criteria in one query - this is sometimes known as specifying compound conditions.

The SQL Logical Operators are listed below:
Logical Operator Meaning
AND This adds another condition to the row filtering effect of the WHERE clause and only returns rows when both conditions return true.
OR This adds another condition to the row filtering effect of the WHERE clause and returns rows that match either of the conditions.
NOT The NOT operator tells the row filtering effect of the WHERE clause to return everything the condition following the NOT have not specified.
Examples of using AND, OR and NOT are given below:


ELECT *
FROM Customers
WHERE NumberOfOrders > 5 AND CountryCode = 'UK'

SELECT *
FROM Customers
WHERE LastUpdated > '01/05/2007' OR NumberOfOrders != 0

SELECT *
FROM Customers
WHERE CountryCode = 'UK' AND NOT IsEmployee = True
 
 
This last example above would return all customers that lived in the UK and that were not employees of the company.
When you use more than one logical operator in the WHERE clause it is advisable to use brackets - () to specify which parts of the clause to evaluate before evaluating other parts. If no brackets are specified then the logical operators in the WHERE clause are evaluated in the following order: (), NOT, AND and then OR. The example of using brackets below specifies to return customers that live in either the UK or France and that have Made more than five orders:



SELECT *
FROM Customers
WHERE (CountryCode = 'UK' OR CountryCode = 'FR')
 AND NumberOfOrders > 5 















If the brackets were omitted as in the example below then the 
customers returned would ALSO include customers from France who had made
 5 or less orders. This is because the AND operator is evaluated before 
the OR operator. Therefore the resultset returns 

 

1) Customers that live in the UK AND have more than 5 orders

OR

2) Customers that live in France






SELECT *
FROM t_Testing
WHERE CountryCode = 'FR' OR CountryCode = 'UK'
 AND NumberOfOrders > 5

You can see how not using brackets to specify the order of evaluation can quickly make understanding the query quite complicated, even with just two Logical operators. Imagine the required understanding if you were using 5 or more logical operators without brackets. Because of this it is always recommended that you use brackets to help your queries be more readable.

Conclusion

In this article we have given an introduction to the SQL WHERE clause that can be used in SELECT, UPDATE and DELETE statements to filter the rows that the query selects / updates / deletes.
Comparison operators enable us to compare values in a rows column to a specified value and choose to return the row or not. Comparison operators include =, <>, !=, > < >= and <=.
The Logical operators AND and OR enable us to specify more than one (compound) comparison conditions in our WHERE clause for more complex filtering. Logical SQL operators include AND, OR and NOT. When using more than one logical operator in the WHERE clause we should use brackets to indicate the order that the compound conditions should be evaluated in.
In the next article we examine some more advanced aspects of the WHERE clause that enable our SELECT, UPDATE and DELETE statements to perform additional functionality.

 

Posted by at 22:00
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)