Latest web development tutorials

SQLite Joins

SQLite isJoins clause to combine two or more tables in the database records.JOIN is a common value by combining two fields in the table means.

SQL defines three main types of connections:

  • Cross-connect - CROSS JOIN

  • En - INNER JOIN

  • External connection - OUTER JOIN

Before we continue, let us assume that there are two tables COMPANY and DEPARTMENT. We've seen used to fill COMPANY table INSERT statement. Now let us assume COMPANY table records listed below:

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

Another table is DEPARTMENT, is defined as follows:

CREATE TABLE DEPARTMENT (
   ID INT PRIMARY KEY NOT NULL,
   DEPT CHAR (50) NOT NULL,
   EMP_ID INT NOT NULL
);

The following is an INSERT statement filled DEPARTMENT table:

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1);

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2);

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7);

Finally, we have the following list of records in the DEPARTMENT table:

ID DEPT EMP_ID
---------- ---------- ----------
1 Billing 1
2 Engineerin 2
3 Finance 7

Cross-connect - CROSS JOIN

Cross-connect (CROSS JOIN) each row of the first table with each row of the second table to match. If both the input table columns x and y, respectively, the result table has x + y columns. Since the cross-connect (CROSS JOIN) may produce a very large table, you must use caution when using them only when appropriate.

Here is the syntax for cross-connect (CROSS JOIN) of:

SELECT ... FROM table1 CROSS JOIN table2 ...

Based on the above table, we can write a cross-connect (CROSS JOIN), as follows:

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

The above query will produce the following results:

EMP_ID NAME DEPT
---------- ---------- ----------
1 Paul IT Billing
2 Paul Engineerin
7 Paul Finance
1 Allen IT Billing
2 Allen Engineerin
7 Allen Finance
1 Teddy IT Billing
2 Teddy Engineerin
7 Teddy Finance
1 Mark IT Billing
2 Mark Engineerin
7 Mark Finance
1 David IT Billing
2 David Engineerin
7 David Finance
1 Kim IT Billing
2 Kim Engineerin
7 Kim Finance
1 James IT Billing
2 James Engineerin
7 James Finance

En - INNER JOIN

Inner connection (INNER JOIN) to create a new result table combine two tables (table1 and table2) join predicate column values. Queries will table1 and table2 in each row in each row are compared to find all rows that satisfy the join predicate matching pairs. When satisfied join predicate column values ​​A and B each matching row of the result will be merged into one row.

Inner connection (INNER JOIN) is the most common type of connection is the default connection type. INNER keyword is optional.

Here is the syntax for an inner join (INNER JOIN) of:

SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

In order to avoid redundancy and keep the wording short, you can use the statement in theUSING expression connection (INNER JOIN) conditions.This expression specifies a list of one or more columns:

SELECT ... FROM table1 JOIN table2 USING (column1, ...) ...

Natural connection (NATURAL JOIN) is similar toJOIN ... USING, but it will equal value between the value of the two tables in each column automatically test for the presence of:

SELECT ... FROM table1 NATURAL JOIN table2 ...

Based on the above table, we can write an inner join (INNER JOIN), as follows:

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

The above query will produce the following results:

EMP_ID NAME DEPT
---------- ---------- ----------
1 Paul IT Billing
2 Allen Engineerin
7 James Finance

External connection - OUTER JOIN

External connection (OUTER JOIN) is an inner join (INNER JOIN) extensions. Although SQL standard defines three types of outer joins: LEFT, RIGHT, FULL, but SQLite supports onlyleft outer join (LEFT OUTER JOIN).

External connection (OUTER JOIN) declared conditions and method of inner connection (INNER JOIN) are the same, the use of ON, USING NATURAL keyword or expression. Initial results table is calculated in the same manner. Once the calculations are completed primary connection, external connections (OUTER JOIN) from one or two tables are not connected to any line in the merge, outer join column with a NULL value, attach them to the results table.

Here is the syntax for a left outer join (LEFT OUTER JOIN) of:

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

In order to avoid redundancy and keep the wording short, you can use theUSING statement expressions outer joins (OUTER JOIN) conditions.This expression specifies a list of one or more columns:

SELECT ... FROM table1 LEFT OUTER JOIN table2 USING (column1, ...) ...

Based on the above table, we can write an external connection (OUTER JOIN), as follows:

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

The above query will produce the following results:

EMP_ID NAME DEPT
---------- ---------- ----------
1 Paul IT Billing
2 Allen Engineerin
            Teddy
            Mark
            David
            Kim
7 James Finance