SQLite Joins statement is utilized to consolidate records from at least two tables in a data set. A JOIN is a methods for consolidating fields from two tables by utilizing values regular to each.
SQL characterizes three significant sorts of joins −
- The CROSS JOIN
- The INNER JOIN
- The OUTER JOIN
Before we continue, how about we consider two tables COMPANY and DEPARTMENT. We as of now have seen INSERT explanations to populate COMPANY table. So how about we accept the rundown of records accessible in COMPANY table −
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 with the accompanying definition −
CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);
Here is the rundown of INSERT articulations to populate 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 );
At last, we have the accompanying rundown of records accessible in DEPARTMENT table −
ID DEPT EMP_ID
---------- ---------- ----------
1 IT Billing 1
2 Engineering 2
3 Finance 7
The CROSS JOIN
CROSS JOIN coordinates each line of the primary table with each line of the subsequent table. On the off chance that the info tables have x and y column, individually, the subsequent table will have x*y line. Since CROSS JOINs can possibly create very huge tables, care should be taken to possibly utilize them when suitable.
Following is the language structure of CROSS JOIN −
SELECT ... FROM table1 CROSS JOIN table2 ...
In view of the above tables, you can compose a CROSS JOIN as follows −
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
The above inquiry will deliver the accompanying outcome −
EMP_ID NAME DEPT
---------- ---------- ----------
1 Paul IT Billing
2 Paul Engineering
7 Paul Finance
1 Allen IT Billing
2 Allen Engineering
7 Allen Finance
1 Teddy IT Billing
2 Teddy Engineering
7 Teddy Finance
1 Mark IT Billing
2 Mark Engineering
7 Mark Finance
1 David IT Billing
2 David Engineering
7 David Finance
1 Kim IT Billing
2 Kim Engineering
7 Kim Finance
1 James IT Billing
2 James Engineering
7 James Finance
The INNER JOIN
Inward JOIN makes another outcome table by consolidating section estimations of two tables (table1 and table2) in view of the join-predicate. The inquiry contrasts each column of table1 and each line of table2 to discover all sets of lines which fulfill the join-predicate. At the point when the join-predicate is fulfilled, the segment esteems for each coordinated pair of columns of An and B are consolidated into an outcome line.
An INNER JOIN is the most well-known and default kind of join. You can utilize INNER catchphrase alternatively.
Following is the sentence structure of INNER JOIN −
SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...
To dodge repetition and keep the stating more limited, INNER JOIN conditions can be announced with a USING articulation. This articulation determines a rundown of at least one sections.
SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...
A NATURAL JOIN is like a JOIN...USING, just it consequently tests for uniformity between the estimations of each segment that exists in the two tables −
SELECT ... FROM table1 NATURAL JOIN table2...
In view of the above tables, you can compose an INNER JOIN as follows −
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
The above inquiry will create the accompanying outcome −
EMP_ID NAME DEPT
---------- ---------- ----------
1 Paul IT Billing
2 Allen Engineering
7 James Financ
The OUTER JOIN
External JOIN is an expansion of INNER JOIN. In spite of the fact that SQL standard characterizes three sorts of OUTER JOINs: LEFT, RIGHT, and FULL, SQLite just backings the LEFT OUTER JOIN.
External JOINs have a condition that is indistinguishable from INNER JOINs, communicated utilizing an ON, USING, or NATURAL watchword. The underlying outcomes table is determined a similar way. When the essential JOIN is determined, an OUTER JOIN will take any unjoined lines from one or the two tables, cushion them out with NULLs, and affix them to the subsequent table.
Following is the punctuation of LEFT OUTER JOIN −
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
To evade repetition and keep the stating more limited, OUTER JOIN conditions can be announced with a USING articulation. This articulation indicates a rundown of at least one sections.
SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...
In light of the above tables, you can compose an internal join as follows −
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
The above inquiry will deliver the accompanying outcome −
EMP_ID NAME DEPT
---------- ---------- ----------
1 Paul IT Billing
2 Allen Engineering
Teddy
Mark
David
Kim
7 James Finance
