Joins Using SQLite3
What is a Join Clause:
A Join clause is used to combine rows from two or more tables, based on related columns between them.
Join Types:
Here are different types of joins used in SQL
Inner Join:
Inner Join returns the records that have matching values in both tables.
First let’s connect to database. Here is the code snippet.
Left Outer Join:
Returns all the records from the left table, and the matched records from the right table.
Right Outer Join:
Returns all the records from the right table, and the matched records from the left table.
Full Outer Join:
Returns all records when there is a match in either left or right table.
Now let’s connect to a database using SQLite3 and demonstrate our joins.
import sqlite3
conn = sqlite3.connect('data.sqlite')
cur = conn.cursor()import pandas as pd
let’s use our join clause on order details and product tables. First let’s have a quick look at these tables first.
# Order details table
cur.execute("""SELECT * FROM orderdetails LIMIT 10;""")
df = pd.DataFrame(cur.fetchall())
df.columns = [i[0] for i in cur.description]
df.head()
# Product table
cur.execute("""SELECT * FROM products LIMIT 10;""")
df = pd.DataFrame(cur.fetchall())
df.columns = [i[0] for i in cur.description]
df.head()
Inner Join Code Snippet:
cur.execute("""SELECT *
FROM orderdetails
INNER JOIN products
ON orderdetails.productCode = products.productCode
LIMIT 10;
""")# Take results and create DataFrame
df = pd.DataFrame(cur.fetchall())
df.columns = [i[0] for i in cur.description]
df.head()
Instead of using ON clause in our joins we can also use USING clause. Here is the quick code snippet.
cur.execute("""SELECT *
FROM orderdetails
INNER JOIN products
USING (productCode)
LIMIT 10;
""")# Take results and create DataFrame
df = pd.DataFrame(cur.fetchall())
df.columns = [i[0] for i in cur.description]
df.head()
Left Join Code Snippet:
cur.execute("""SELECT *
FROM orderdetails
LEFT JOIN products
ON orderdetails.productCode = products.productCode
LIMIT 10;
""")# Take results and create DataFrame
df = pd.DataFrame(cur.fetchall())
df.columns = [i[0] for i in cur.description]
df.head()
Right Join Code Snippet:
cur.execute("""SELECT *
FROM orderdetails
RIGHT JOIN products
ON orderdetails.productCode = products.productCode
LIMIT 10;
""")# Take results and create DataFrame
df = pd.DataFrame(cur.fetchall())
df.columns = [i[0] for i in cur.description]
df.head()
Full Join Code Snippet:
cur.execute("""SELECT *
FROM orderdetails
FULL OUTER JOIN products
ON orderdetails.productCode = products.productCode
LIMIT 10;
""")# Take results and create DataFrame
df = pd.DataFrame(cur.fetchall())
df.columns = [i[0] for i in cur.description]
df.head()
Unfortunately SQLite does not support Right Join Clause and Full Outer Join Clause. How ever you can easily emulate the Full Outer Join by using Left Join Clause.
cur.execute("""SELECT *
FROM orderdetails
LEFT JOIN products
USING (productCode)
UNION ALL
SELECT *
FROM products
LEFT JOIN orderdetails
USING (productCode)
LIMIT 10;
""")# Take results and create DataFrame
df = pd.DataFrame(cur.fetchall())
df.columns = [i[0] for i in cur.description]
df.head()
I understand it is a very basic introduction to Joins using SQLite. I plan to write more detailed blogs on advanced techniques like CTE, Stored Procedures, Trigger, Views.
Happy Reading!!
References:
