A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

A transaction is the propagation of one or more changes to the database. For example, if you are creating, updating, or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.

Practically, you will club many SQLite queries into a group and you will execute all of them together as part of a transaction.

Properties of Transactions

Transactions have the following four standard properties, usually referred to by the acronym ACID.

• Atomicity − Ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure and previous operations are rolled back to their former state.

• Consistency − Ensures that the database properly changes states upon a successfully committed transaction.

• Isolation − Enables transactions to operate independently of and transparent to each other.

• Durability − Ensures that the result or effect of a committed transaction persists in case of a system failure.

Transaction Control

Following are the following commands used to control transactions:

• BEGIN TRANSACTION − To start a transaction.

• COMMIT − To save the changes, alternatively you can use END TRANSACTION command.

• ROLLBACK − To rollback the changes.

Transactional control commands are only used with DML commands INSERT, UPDATE, and DELETE. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database.

BEGIN TRANSACTION Command

Transactions can be started using BEGIN TRANSACTION or simply BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command is encountered. However, a transaction will also ROLLBACK if the database is closed or if an error occurs. Following is the simple syntax to start a transaction.

BEGIN;
or
BEGIN TRANSACTION;

COMMIT Command

COMMIT command is the transactional command used to save changes invoked by a transaction to the database.

COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.

Following is the syntax for COMMIT command.

COMMIT;
or
END TRANSACTION;

ROLLBACK Command

ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.

ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

Following is the syntax for ROLLBACK command.

ROLLBACK;

Example

Consider table with the following records.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Now, let”s start a transaction and delete records from the table having age = 25. Then, use ROLLBACK command to undo all the changes.

sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> ROLLBACK;

Now, if you check COMPANY table, it still has the following records −

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Let”s start another transaction and delete records from the table having age = 25 and finally we use COMMIT command to commit all the changes.

sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> COMMIT;

If you now check COMPANY table is still has the following records −

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A view is nothing more than a SQLite statement that is stored in the database with an associated name. It is actually a composition of a table in the form of a predefined SQLite query.

A view can contain all rows of a table or selected rows from one or more tables. A view can be created from one or many tables which depends on the written SQLite query to create a view.

Views which are kind of virtual tables, allow the users to −

• Structure data in a way that users or classes of users find natural or intuitive.

• Restrict access to the data such that a user can only see limited data instead of a complete table.

• Summarize data from various tables, which can be used to generate reports.

SQLite views are read-only and thus you may not be able to execute a DELETE, INSERT or UPDATE statement on a view. However, you can create a trigger on a view that fires on an attempt to DELETE, INSERT, or UPDATE a view and do what you need in the body of the trigger.

Creating Views

SQLite views are created using the CREATE VIEW statement. SQLite views can be created from a single table, multiple tables, or another view.

Following is the basic CREATE VIEW syntax.

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

You can include multiple tables in your SELECT statement in a similar way as you use them in a normal SQL SELECT query. If the optional TEMP or TEMPORARY keyword is present, the view will be created in the temp database.

Example

Consider table with the following records −

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Following is an example to create a view from COMPANY table. This view will be used to have only a few columns from COMPANY table.

sqlite> CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;

You can now query COMPANY_VIEW in a similar way as you query an actual table. Following is an example −

sqlite> SELECT * FROM COMPANY_VIEW;

This will produce the following result.

ID          NAME        AGE
----------  ----------  ----------
1           Paul        32
2           Allen       25
3           Teddy       23
4           Mark        25
5           David       27
6           Kim         22
7           James       24

Dropping Views

To drop a view, simply use the DROP VIEW statement with the view_name. The basic DROP VIEW syntax is as follows −

sqlite> DROP VIEW view_name;

The following command will delete COMPANY_VIEW view, which we created in the last section.

sqlite> DROP VIEW COMPANY_VIEW;

Unfortunately, we do not have TRUNCATE TABLE command in SQLite but you can use SQLite DELETE command to delete complete data from an existing table, though it is recommended to use DROP TABLE command to drop the complete table and re-create it once again.

Syntax

Following is the basic syntax of DELETE command.

sqlite> DELETE FROM table_name;

Following is the basic syntax of DROP TABLE.

sqlite> DROP TABLE table_name;

If you are using DELETE TABLE command to delete all the records, it is recommended to use VACUUM command to clear unused space.

Example

Consider table with the following records.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Following is the example to truncate the above table −

SQLite> DELETE FROM COMPANY;
SQLite> VACUUM;

Now, COMPANY table is truncated completely and nothing will be the output from SELECT statement.

The “INDEXED BY index-name” clause specifies that the named index must be used in order to look up values on the preceding table.

If index-name does not exist or cannot be used for the query, then the preparation of the SQLite statement fails.

The “NOT INDEXED” clause specifies that no index shall be used when accessing the preceding table, including implied indices created by UNIQUE and PRIMARY KEY constraints.

However, the INTEGER PRIMARY KEY can still be used to look up entries even when “NOT INDEXED” is specified.

Syntax

Following is the syntax for INDEXED BY clause and it can be used with DELETE, UPDATE or SELECT statement.

SELECT|DELETE|UPDATE column1, column2...
INDEXED BY (index_name)
table_name
WHERE (CONDITION);

Example

Consider table We will create an index and use it for performing INDEXED BY operation.

sqlite> CREATE INDEX salary_index ON COMPANY(salary);
sqlite>

Now selecting the data from table COMPANY you can use INDEXED BY clause as follows −

sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;

This will produce the following result.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
7           James       24          Houston     10000.0
2           Allen       25          Texas       15000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

SQLite ALTER TABLE command modifies an existing table without performing a full dump and reload of the data. You can rename a table using ALTER TABLE statement and additional columns can be added in an existing table using ALTER TABLE statement.

There is no other operation supported by ALTER TABLE command in SQLite except renaming a table and adding a column in an existing table.

Syntax

Following is the basic syntax of ALTER TABLE to RENAME an existing table.

ALTER TABLE database_name.table_name RENAME TO new_table_name;

Following is the basic syntax of ALTER TABLE to add a new column in an existing table.

ALTER TABLE database_name.table_name ADD COLUMN column_def...;

Example

Consider the table with the following records −

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Now, let”s try to rename this table using ALTER TABLE statement as follows −

sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;

The above SQLite statement will rename COMPANY table to OLD_COMPANY. Now, let”s try to add a new column in OLD_COMPANY table as follows −

sqlite> ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);

COMPANY table is now changed and following will be the output from SELECT statement.

ID          NAME        AGE         ADDRESS     SALARY      SEX
----------  ----------  ----------  ----------  ----------  ---
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

It should be noted that newly added column is filled with NULL values.

SQLite NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank.

A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.

Syntax

Following is the basic syntax of using NULL while creating a table.

SQLite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Here, NOT NULL signifies that the column should always accept an explicit value of the given data type. There are two columns where we did not use NOT NULL which means these columns could be NULL.

A field with a NULL value is one that has been left blank during record creation.

Example

The NULL value can cause problems when selecting data, because when comparing an unknown value to any other value, the result is always unknown and not included in the final results. Consider the following table, with the following records −

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Let us use UPDATE statement to set a few nullable values as NULL as follows −

sqlite> UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);

Now, COMPANY table will have the following records.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22
7           James       24

Next, let us see the usage of IS NOT NULL operator to list down all the records where SALARY is not NULL.

sqlite> SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NOT NULL;

The above SQLite statement will produce the following result −

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

Following is the usage of IS NULL operator, which will list down all the records where SALARY is NULL.

sqlite> SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NULL;

The above SQLite statement will produce the following result.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22
7           James       24

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

For example, if you want to reference all pages in a book that discuss a certain topic, you first refer to the index, which lists all topics alphabetically and are then referred to one or more specific page numbers.

An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.

Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in an ascending or descending order.

Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there”s an index.

The CREATE INDEX Command

Following is the basic syntax of CREATE INDEX.

CREATE INDEX index_name ON table_name;

Single-Column Indexes

A single-column index is one that is created based on only one table column. The basic syntax is as follows −

CREATE INDEX index_name
ON table_name (column_name);

Unique Indexes

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows −

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Composite Indexes

A composite index is an index on two or more columns of a table. The basic syntax is as follows −

CREATE INDEX index_name
on table_name (column1, column2);

Whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very frequently in a query”s WHERE clause as filter conditions.

Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, the composite index would be the best choice.

Implicit Indexes

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

Example

Following is an example where we will create an index in table for salary column −

sqlite> CREATE INDEX salary_index ON COMPANY (salary);

Now, let”s list down all the indices available in COMPANY table using .indices command as follows −

sqlite> .indices COMPANY

This will produce the following result, where sqlite_autoindex_COMPANY_1 is an implicit index which got created when the table itself was created.

salary_index
sqlite_autoindex_COMPANY_1

You can list down all the indexes database wide as follows −

sqlite> SELECT * FROM sqlite_master WHERE type = ''index

The DROP INDEX Command

An index can be dropped using SQLite DROP command. Care should be taken when dropping an index because performance may be slowed or improved.

Following is the basic syntax is as follows −

DROP INDEX index_name;

You can use the following statement to delete previously created index.

sqlite> DROP INDEX salary_index;

When Should Indexes Be Avoided?

Although indexes are intended to enhance the performance of a database, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered.

Indexes should not be used in −

• Small tables.
• Tables that have frequent, large batch update or insert operations.
• Columns that contain a high number of NULL values.
• Columns that are frequently manipulated.

SQLite Triggers are database callback functions, which are automatically performed/invoked when a specified database event occurs. Following are the important points about SQLite triggers −

• SQLite trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a particular database table occurs or whenever an UPDATE occurs on one or more specified columns of a table.

• At this time, SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers. Hence, explicitly specifying FOR EACH ROW is optional.

• Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted, or updated using references of the form NEW.column-name and OLD.column-name, where column-name is the name of a column from the table that the trigger is associated with.

• If a WHEN clause is supplied, the SQL statements specified are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the SQL statements are executed for all rows.

• The BEFORE or AFTER keyword determines when the trigger actions will be executed relative to the insertion, modification, or removal of the associated row.

• Triggers are automatically dropped when the table that they are associated with is dropped.

• The table to be modified must exist in the same database as the table or view to which the trigger is attached and one must use just tablename not database.tablename.

• A special SQL function RAISE() may be used within a trigger-program to raise an exception.

Syntax

Following is the basic syntax of creating a trigger.

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

Here, event_name could be INSERT, DELETE, and UPDATE database operation on the mentioned table table_name. You can optionally specify FOR EACH ROW after table name.

Following is the syntax for creating a trigger on an UPDATE operation on one or more specified columns of a table.

CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
BEGIN
   -- Trigger logic goes here....
END;

Example

Let us consider a case where we want to keep audit trial for every record being inserted in COMPANY table, which we create newly as follows (Drop COMPANY table if you already have it).

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

To keep audit trial, we will create a new table called AUDIT where the log messages will be inserted, whenever there is an entry in COMPANY table for a new record.

sqlite> CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

Here, ID is the AUDIT record ID, and EMP_ID is the ID which will come from COMPANY table and DATE will keep timestamp when the record will be created in COMPANY table. Now let”s create a trigger on COMPANY table as follows −

sqlite> CREATE TRIGGER audit_log AFTER INSERT
ON COMPANY
BEGIN
   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime(''now''));
END;

Now, we will start actual work, Let”s start inserting record in COMPANY table which should result in creating an audit log record in AUDIT table. Create one record in COMPANY table as follows −

sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, ''Paul'', 32, ''California'', 20000.00 );

This will create one record in COMPANY table, which is as follows −

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0

Same time, one record will be created in AUDIT table. This record is the result of a trigger, which we have created on INSERT operation in COMPANY table. Similarly, you can create your triggers on UPDATE and DELETE operations based on your requirements.

EMP_ID      ENTRY_DATE
----------  -------------------
1           2013-04-05 06:26:00

Listing Triggers

You can list down all the triggers from sqlite_master table as follows −

sqlite> SELECT name FROM sqlite_master
WHERE type = ''trigger

The above SQLite statement will list down only one entry as follows −

name
----------
audit_log

If you want to list down triggers on a particular table, then use AND clause with table name as follows −

sqlite> SELECT name FROM sqlite_master
WHERE type = ''trigger'' AND tbl_name = ''COMPANY

The above SQLite statement will also list down only one entry as follows −

name
----------
audit_log

Dropping Triggers

Following is the DROP command, which can be used to drop an existing trigger.

sqlite> DROP TRIGGER trigger_name;

You can rename a table or a column temporarily by giving another name, which is known as ALIAS. The use of table aliases means to rename a table in a particular SQLite statement. Renaming is a temporary change and the actual table name does not change in the database.

The column aliases are used to rename a table”s columns for the purpose of a particular SQLite query.

Syntax

Following is the basic syntax of table alias.

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

Following is the basic syntax of column alias.

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

Example

Consider the following two tables, (a) table is as follows −

sqlite> select * from COMPANY;
ID          NAME                  AGE         ADDRESS     SALARY
----------  --------------------  ----------  ----------  ----------
1           Paul                  32          California  20000.0
2           Allen                 25          Texas       15000.0
3           Teddy                 23          Norway      20000.0
4           Mark                  25          Rich-Mond   65000.0
5           David                 27          Texas       85000.0
6           Kim                   22          South-Hall  45000.0
7           James                 24          Houston     10000.0

(b) Another table is as follows −

ID          DEPT                  EMP_ID
----------  --------------------  ----------
1           IT Billing            1
2           Engineering           2
3           Finance               7
4           Engineering           3
5           Finance               4
6           Engineering           5
7           Finance               6

Now, following is the usage of TABLE ALIAS where we use C and D as aliases for COMPANY and DEPARTMENT tables respectively −

sqlite> SELECT C.ID, C.NAME, C.AGE, D.DEPT
        FROM COMPANY AS C, DEPARTMENT AS D
        WHERE  C.ID = D.EMP_ID;

The above SQLite statement will produce the following result −

ID          NAME        AGE         DEPT
----------  ----------  ----------  ----------
1           Paul        32          IT Billing
2           Allen       25          Engineering
3           Teddy       23          Engineering
4           Mark        25          Finance
5           David       27          Engineering
6           Kim         22          Finance
7           James       24          Finance

Consider an example for the usage of COLUMN ALIAS where COMPANY_ID is an alias of ID column and COMPANY_NAME is an alias of name column.

sqlite> SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT
        FROM COMPANY AS C, DEPARTMENT AS D
        WHERE  C.ID = D.EMP_ID;

The above SQLite statement will produce the following result −

COMPANY_ID  COMPANY_NAME  AGE         DEPT
----------  ------------  ----------  ----------
1           Paul          32          IT Billing
2           Allen         25          Engineering
3           Teddy         23          Engineering
4           Mark          25          Finance
5           David         27          Engineering
6           Kim           22          Finance
7           James         24          Finance

SQLite UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows.

To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order, but they do not have to be of the same length.

Syntax

Following is the basic syntax of UNION.

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Here the given condition could be any given expression based on your requirement.

Example

Consider the following two tables, (a) table as follows −

sqlite> select * from COMPANY;
ID          NAME                  AGE         ADDRESS     SALARY
----------  --------------------  ----------  ----------  ----------
1           Paul                  32          California  20000.0
2           Allen                 25          Texas       15000.0
3           Teddy                 23          Norway      20000.0
4           Mark                  25          Rich-Mond   65000.0
5           David                 27          Texas       85000.0
6           Kim                   22          South-Hall  45000.0
7           James                 24          Houston     10000.0

(b) Another table is as follows −

ID          DEPT                  EMP_ID
----------  --------------------  ----------
1           IT Billing            1
2           Engineering           2
3           Finance               7
4           Engineering           3
5           Finance               4
6           Engineering           5
7           Finance               6

Now let us join these two tables using SELECT statement along with UNION clause as follows −

sqlite>  SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID

         UNION

         SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

This will produce the following result.

EMP_ID      NAME                  DEPT
----------  --------------------  ----------
1           Paul                  IT Billing
2           Allen                 Engineering
3           Teddy                 Engineering
4           Mark                  Finance
5           David                 Engineering
6           Kim                   Finance
7           James                 Finance

The UNION ALL Clause

The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows.

The same rules that apply to UNION apply to the UNION ALL operator as well.

Syntax

Following is the basic syntax of UNION ALL.

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Here the given condition could be any given expression based on your requirement.

Example

Now, let us join the above-mentioned two tables in our SELECT statement as follows −

sqlite>  SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID

         UNION ALL

         SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

This will produce the following result.

EMP_ID      NAME                  DEPT
----------  --------------------  ----------
1           Paul                  IT Billing
2           Allen                 Engineering
3           Teddy                 Engineering
4           Mark                  Finance
5           David                 Engineering
6           Kim                   Finance
7           James                 Finance
1           Paul                  IT Billing
2           Allen                 Engineering
3           Teddy                 Engineering
4           Mark                  Finance
5           David                 Engineering
6           Kim                   Finance
7           James                 Finance