MySQL Logical Operators

The logical operators are typically used to combine conditional statements. Below is the list of logical operators in MySQL.

MySQL Logical Operators Examples

CREATE TABLE EMP

(EMPNO NUMERIC(4) ,

ENAME VARCHAR(10),

JOB VARCHAR(9),

MGR NUMERIC(4),

HIREDATE DATE,

SAL NUMERIC(7,2),

CONSTRAINT PK_EMP PRIMARY KEY(EMPNO)

);

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800);

INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600);

INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-2-22',1250);

INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-4-2',2975);

INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250);

INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850);

INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-6-9',2450);

INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-4-19',3000);

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000);

INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500);

INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-5-23',1100);

INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-3',950);

INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-3',3000);

INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-1-23',1300);

COMMIT;

SELECT ENAME FROM EMP WHERE SAL BETWEEN 100 AND 1000

SELECT EMPNO, ENAME, JOB, MGR FROM EMP WHERE ENAME IN ('SMITH','ALLEN','WARD');

SELECT EMPNO, ENAME, JOB FROM EMP WHERE JOB LIKE ('CLE%')

SELECT ENAME, SAL FROM EMP WHERE EXISTS (SELECT * FROM EMP WHERE SAL >3000)

SELECT ENAME,SAL FROM EMP WHERE EXISTS (SELECT * FROM EMP WHERE SAL =3333)

SELECT ENAME, SAL FROM EMP WHERE SAL > ALL (SELECT SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000)

SELECT ENAME ,SAL FROM EMP WHERE SAL <ALL (SELECT SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000)

SELECT ENAME,SAL FROM EMP WHERE SAL < ALL (SELECT SAL FROM EMP WHERE SAL BETWEEN 900 AND 3000)

SELECT ENAME, SAL FROM EMP WHERE SAL < ANY (SELECT SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000)

All Chapters