SQLite Except Operator

Here we will learn SQLite Except operator with example and how to use SQLite Except operator to return all the rows from a first select statement except the records returned by a second select statement with examples. 

SQLite Except Operator

In SQLite, the Except operator is useful to exclude some rows while returning from a select statement. Suppose if we use Except operator with two SQLite Select statements then it will return all the records from a first select statement except the records which exist in the second Select statement.

 

Generally, we use SQLite Except operator with Select statements and all the Select statements which are using with Except operator must have the same number of fields.

 

Following is the pictorial representation of SQLite Except operator.

 

SQLite Except Operator Pictorial Representation with Example

 

If you observe above SQLite Except operator diagram it will return the records which are in a color shaded area that means Except operator will return all the records from first Select statement except the records which exist in second Select statement.

Syntax of SQLite Except Operator

Following is the syntax of using SQLite Except operator with a select statement.

 

SELECT column1, column2,..., columnn

FROM table-list

[WHERE condition]

 

EXCEPT

 

SELECT column1, column2,..., columnn

FROM table-list

[WHERE condition];

If you observe above SQLite Except operator syntax we defined two Select statements with Except operator. Here SQLite Except operator will return rows from first Select statement which does not exist in the second Select statement. 

 

In above Except operator syntax, we defined some of the properties that are

 

  • columns - It may be no. of column or expression that you want as a result. 
  • table-list - It may be a list of a table from which you want results. 
  • Where Condition - Its optional. We can use this condition based on our requirements in Select statements.

SQLite Except Operator Example

We will see how to use SQLite Except operator with SELECT statement for that first create one table called dept_master as follows.

 

CREATE TABLE dept_master

(dept_id INTEGER PRIMARY KEY AUTOINCREMENT,

dept_name TEXT);

 

INSERT INTO dept_master(dept_name)

VALUES('Admin'),

('Sales'),

('Quality Control');

('Marketing');

The same way create emp_master table and insert some records like as shown below.

 

CREATE TABLE emp_master

(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT,

last_name TEXT,

salary NUMERIC,

dept_id INTEGER references dept_master);

 

INSERT INTO emp_master

values (1,'Honey','Patel',10100,1),

(2,'Shweta','Jariwala', 19300,2),

(3,'Vinay','Jariwala', 35100,3),

(4,'Jagruti','Viras', 9500,12);

Now we will see how to use SQLite Except operator with a single column in Select statements for that write SQLite query like as shown following.

 

SELECT dept_id

FROM dept_master

 

EXCEPT

 

SELECT dept_id

FROM emp_master

When we run above sqlite Except operator query it will return dept_id from dept_master table which are not exists in emp_master table like as shown below.

 

dept_id

----------

4

SQLite Except with Multiple Columns

In case if we have multiple columns in SQLite Select statement then the Except operator will use all the columns of the first Select statement to compare values with the second Select statement.

 

Following is the example of using SQLite Except operator with multiple columns in Select statement.

 

SELECT dept_id, dept_name

FROM table1

 

EXCEPT

 

SELECT dept_id, dept_name

FROM table2

Here above SQLite Except operator will return records of table1 which are not matching with  dept_id, dept_name column values of table2.

 

This is how we can use SQLite Except operator to get records from Select statements based on our requirements.

Previous
 
Next