MySQL AND & OR Conditions

AND and OR are logical operators used to create complex conditions for filtering data in a SELECT statement.

AND operator is used to retrieve records that meet all specified conditions.

For example, if we want to retrieve records where both the Name and the Lastname match the specified criteria, we would use the AND operator. The syntax would be: 

SELECT * FROM table_name WHERE condition1 AND condition2;

On the other hand, OR operator is used to retrieve records that meet at least one of the specified conditions.

For example, if we want to retrieve records where either the Name or the Lastname match the specified criteria, we would use the OR operator. The syntax would be:

SELECT * FROM table_name WHERE condition1 OR condition2;

Examples

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
| 13 | John      | Smith    |    111333 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

In MySQL, single quotes are used to enclose string values. This is necessary to distinguish string values from other types of values, such as numeric values or column names. 

mysql> SELECT * FROM friends WHERE Name='Larry' AND Lastname='TheFirst';
+----+-------+----------+-----------+
| id | Name  | Lastname | Telephone |
+----+-------+----------+-----------+
| 10 | Larry | TheFirst |    555888 |
+----+-------+----------+-----------+
1 row in set (0.00 sec)

mysql>

The output of the above query shows all the columns of the row that matches the conditions specified in the WHERE clause, i.e., the name is "Larry" and the last name is "TheFirst".

There is only one row that meets these conditions, so the output contains only that row.

mysql> SELECT * FROM friends WHERE Name='Larry' AND Name='John';
Empty set (0.00 sec)

mysql>

The output of the above query is an empty set, as there is no row in the table where the name is both "Larry" and "John" at the same time.

The condition Name='Larry' and Name='John' are contradictory, and no row can satisfy both conditions simultaneously. 

mysql> SELECT * FROM friends WHERE id=12 AND Telephone=444888;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 12 | Bill | Gates    |    444888 |
+----+------+----------+-----------+
1 row in set (0.00 sec)

mysql>

You should not use single quotes around integer values in MySQL tables. This is because MySQL will treat the value as a string, rather than a numeric value, and may cause unexpected behavior in queries or calculations.

In general, you should use quotes only around string values and omit them for numeric values and other data types such as dates, times, and booleans. 

mysql> SELECT Name FROM friends WHERE id=13 AND Lastname='Smith';
+------+
| Name |
+------+
| John |
+------+
1 row in set (0.00 sec)

mysql>

We will experiment with more requests. Select all: 

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
| 13 | John      | Smith    |    111333 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

Where and or combinations: 

mysql> SELECT * FROM friends WHERE Name='John' OR Telephone=555444;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
| 13 | John      | Smith    |    111333 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
3 rows in set (0.00 sec)

mysql>

mysql> SELECT * FROM friends WHERE id=3 OR Lastname='Some';
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  3 | Samantha  | Fox      |    456456 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
2 rows in set (0.00 sec)

mysql>