Search Marketing

   
   

Using OR, || & AND, && in Select Query Statements

Thu Nov 03, 2011 7:24 am
<<    
Comments: 2 Views: 5350

MySQL select statements allow the use of “OR” or “||” to choose two or more possibilities in a single statement.

We will use the following database table as an example.

mysql> select * from Name;
+----+--------+---------------------+
| ID | Name | StartDate |
+----+--------+---------------------+
| 1 | Joe | 2001-02-12 00:00:00 |
| 2 | Jack | 2003-03-15 00:00:00 |
| 3 | John | 1980-05-15 00:00:00 |
| 4 | James | 1994-09-21 00:00:00 |
| 5 | George | 1996-12-11 00:00:00 |
+----+--------+---------------------+

1. If we wanted to select a single matching date from this table of information then we might write something like the following.

mysql> select * from Name where left(Date,3)='200' && Name like '%J%';
+----+------+---------------------+
| ID | Name | Date |
+----+------+---------------------+
| 1 | Joe | 2001-02-12 00:00:00 |
| 2 | Jack | 2003-03-15 00:00:00 |
+----+------+---------------------+

You may notice we added “Name like '%J%'” along with the left(Date,3)='200'.

2. If we wanted to get either a 21st century date or a 1990's date, we might use the following.

mysql> select * from Name where left(Date,3)='200' && Name like '%J%' || left(Date,3)='199';
+----+--------+---------------------+
| ID | Name | Date |
+----+--------+---------------------+
| 1 | Joe | 2001-02-12 00:00:00 |
| 2 | Jack | 2003-03-15 00:00:00 |
| 4 | James | 1994-09-21 00:00:00 |
| 5 | George | 1996-12-11 00:00:00 |
+----+--------+---------------------+

Here you should notice that the name “George” was also pulled in, despite the fact that we selected “Name like '%J%'”. Using the “||” or “OR” option in the select forces both sides of the “||” to be literally selected.

To achieve the desired result of 21st century or 1990's dates and only names that begin with the letter “J”, we must use the following statement.

mysql> select * from Name where left(Date,3)='200' && Name like '%J%' || left(Date,3)='199' && Name like '%J%';
+----+-------+---------------------+
| ID | Name | Date |
+----+-------+---------------------+
| 1 | Joe | 2001-02-12 00:00:00 |
| 2 | Jack | 2003-03-15 00:00:00 |
| 4 | James | 1994-09-21 00:00:00 |
+----+-------+---------------------+

By mixing the “||” and “&&” switches you can find data in a single table or multiple tables.



Comments
Wed May 12, 2010 2:44 am
Name: | Comment:

Thu Nov 03, 2011 7:24 am
Name: | Comment:

Name:
*
URL: http: (ex. cnn.com)
Comment:
*
Number:
Math (20 + 5)
* required
SubCategories
Books

© 2017 Christonium LLC

Christonium.com
|
Terms of Use
|
Privacy
ccc