SQLite - SPLessons

SQLite Joins

Chapter 13

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQLite Joins

SQLite Joins

shape Description

SQLite Joins are queries which combines data from multiple tables. SQLite Joins are performed at whatever points, when two or more tables are joined in a SQLite statement.

Join can be divided into

Cross join

shape Description

A Cross join in SQLite Joins is a join of every row of one table to every row of another table.

shape Syntax

Select * from <table_name1> cross join <table_name2>;

table_name => Any accurate table.

shape Examples

By viewing the below example, the concept of Cross join understands easily.

sqlite> create table employee02(emp_id int,ename varchar2(20),deptno int);
sqlite> insert into employee02 values(1001,'Mike',12);
sqlite> insert into employee02 values(10012,'Maddi',13);
sqlite> insert into employee02 values(10013,'Make',12);
sqlite> insert into employee02 values(10014,'Jake',13);
sqlite> select * from employee02;
1001|Mike|12
10012|Maddi|13
10013|Make|12
10014|Jake|13
sqlite> create table dept(deptno int,dept_name varchar2(20),dept_city varchar2(20));
sqlite> insert into dept values(12,'Joy','Texas');
sqlite> insert into dept values(13,'Charley','Newyork');
sqlite> select * from dept;
12|Joy|Texas
13|Charley|Newyork
sqlite> select * from employee02 cross join dept;
1001|Mike|12|12|Joy|Texas
1001|Mike|12|13|Charley|Newyork
10012|Maddi|13|12|Joy|Texas
10012|Maddi|13|13|Charley|Newyork
10013|Make|12|12|Joy|Texas
10013|Make|12|13|Charley|Newyork
10014|Jake|13|12|Joy|Texas
10014|Jake|13|13|Charley|Newyork

In the above example, the Cross join combines both employee02 table and dept table and update the values to all the fields based on depno, so that each and every employee will have completed details with duplicate values.

Inner join

shape Description

SQLite Inner Join gives back all columns from different tables where the join case is confront.

shape Syntax

Select columns from table1 Inner join table2 on table1.column=table2.column;


Table_Name => Accurate table name

shape Examples

By viewing the below example, the concept of Cross join understands easily.

sqlite> create table emp(employee_id int,last_name varchar2(20),first_name varch
ar2(20),position_id int);
sqlite> insert into emp values(10,'Jack','Mail',1);
sqlite> insert into emp values(20,'Martin','Prience',2);
sqlite> insert into emp values(30,'Hunt','Deelel',3);
sqlite> insert into emp values(40,'Peter','Dsmith',4);
sqlite> insert into emp values(50,'Mike', 'Jain',Null);
sqlite> select * from emp;
10|Jack|Mail|1
20|Martin|Prience|2
30|Hunt|Deelel|3
40|Peter|Dsmith|4
50|Mike|Jain|
sqlite> create table position(position_id int,job varchar2(20));
sqlite> insert into position values(1,'Accounts');
sqlite> insert into position values(2,'Manager');
sqlite> insert into position values(3,'Programmer Analyst');
sqlite> insert into position values(4,'Data Analyst');
sqlite> insert into position values(5,'Marketing');
sqlite> select * from position;
1|Accounts
2|Manager
3|Programmer Analyst
4|Data Analyst
5|Marketing
sqlite> select emp.employee_id,emp.last_name,position.job from emp INNER JOIN position ON emp.position_id=position.position_id;
10|Jack|Accounts
20|Martin|Manager
30|Hunt|Programmer Analyst
40|Peter|Data Analyst

Here in the above example, it omits the employee_id(50) and last_name(Mike) from emp table as it doesn’t contain position_id values in emp and inner join operation will not display that rows details.

Left Outer Join

shape Description

Left outer join of SQLite Joins will give back all rows from the left hand side indicating the condition those rows from the other table where the joined fields are equivalent.

shape Syntax

Select columns from table1 Left[Outer]join table2 on table1.column = table2.column;

table name => Any accurate table name

shape Examples

sqlite> select * from emp;
10|Jack|Mail|1
20|Martin|Prience|2
30|Hunt|Deelel|3
40|Peter|Dsmith|4
50|Mike|Jain|
sqlite> select * from position;
1|Accounts
2|Manager
3|Programmer Analyst
4|Data Analyst
sqlite> select emp.employee_id,emp.last_name,position.job from emp Left Outer Jo
in position on emp.position_id=position.position_id;
10|Jack|Accounts
20|Martin|Manager
30|Hunt|Programmer Analyst
40|Peter|Data Analyst
50|Mike|

In Left outer join the row for employee_id 50 will also be displayed in left outer join and it will display the job field as null because there is no particular row exit in position table.

Summary

shape Key Points

  • SQLite Joins – Join is a query which combines data from multiple table.
  • Cross Join – Join of every row of one table to every row of another table.
  • Inner join – Gives back all the columns from different tables where the case is confront.
  • Left outer join – Will give back all the rows from the left hand side indicating the condition those rows from the other table.