SQL AND and OR Operators

SQL AND and OR operators use for filter the records based on more than one condition.

SQL AND condition use to test more then one conditions in INSERT, UPDATE, DELETE, SELECT statement. AND operator work is test first condition if true come to a second and so forth, otherwise not check next condition.

SQL AND Operator filter the record based table data. INSERT, UPDATE, DELETE, SELECT statement perform only when all specified condition TRUE.

Syntax

Considering following general syntax,

WHERE condition_1 
AND condition_2
AND condition_3
...
AND condtion_N;

Example

SELECT Statement: Select table data only condition matched data,

SQL> SELECT * FROM users_info WHERE name = 'Max Miller' AND no = 2;

 NO NAME                ADDRESS                     CONTACT_NO
--- ------------------- --------------------------- --------------
  2 Max Miller          41 NEW ROAD.                000-444-8292

INSERT Statement: AND operator use in INSERT statement, INSERT only condition matched data,

SQL> INSERT INTO userinfo (no, name, address) 
     SELECT no, name, address FROM users_info WHERE no = 1 AND name = 'Opal Kole';

1 row inserted.

UPDATE Statement: AND operator use in UPDATE statement, Update only condition matched rows,

SQL> UPDATE users_info SET address = '145 Taxo court.' WHERE no = 10 AND name = 'Sariya Vargas';

1 row updated.

DELETE Statement: AND operator use in DELETE statement, DELETE only condition matched rows but in this example no is find but name 'Sariya' not exist in table so 0 row deleted.

SQL> DELETE FROM users_info WHERE no = 10 AND name = 'Sariya';

0 row deleted.

SQL OR Condition Statement

SQL OR Condition use to test more then one conditions in INSERT, UPDATE, DELETE, SELECT statement. OR operator test all condition even if condition TRUE or FALSE. And return data when any one of the condition TRUE.

SQL OR Operator same as AND operator, return the record base filtered data. INSERT, UPDATE, DELETE, SELECT statement perform only one of the specified condition TRUE.

Syntax

Considering following general syntax,

WHERE condition_1 
OR condition_2
OR condition_3
...
OR condtion_N;

Example

SELECT Statement: Select table data if any one of the condition true. In this example select all table data, whose name 'Max Miller' and no is 3. Both are different condition and found in table so return both satisfied condition data.

SQL> SELECT * FROM users_info WHERE name = 'Max Miller' OR no = 3;

 NO NAME                ADDRESS                     CONTACT_NO
--- ------------------- --------------------------- --------------
  2 Max Miller          41 NEW ROAD.                000-444-8292
  3 Beccaa Moss         2500 green city.            000-444-7586

INSERT Statement: OR operator use in INSERT statement, INSERT when any one of the condition TRUE, In this example new record inserted from another table only those rows whose no column value is 1 OR name value 'Max Miller'.

SQL> INSERT INTO userinfo (no, name, address) 
     SELECT no, name, address FROM users_info WHERE no = 1 OR name = 'Max Miller';

2 row inserted.

UPDATE Statement: OR operator use in UPDATE statement, Update when any one if the condition TRUE, In this example address update only those rows whose no column value is 10 OR name value 'Sariya'.

SQL> UPDATE users_info SET address = '145 Taxo court.' WHERE no = 10 OR name = 'Sariya';

1 row updated.

DELETE Statement: AND operator use in DELETE statement, DELETE only condition matched rows but in this example no is find but name 'Sariya' not exist in table so 0 row deleted.

SQL> DELETE FROM users_info WHERE no = 10 AND name = 'Sariya';

0 row deleted.