3

SQL Joins Explained Using Pseudo (Python) Code

 3 years ago
source link: https://snakeycode.wordpress.com/2019/08/01/sql-joins-explained-using-pseudo-python-code/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

SQL Joins Explained Using Pseudo (Python) Code

There are many SQL JOIN tutorials that explain joins using Venn diagrams and/or very clean, sensible data. For me, those tutorials are not very intuitive. So here is my attempt at explaining them using Python as pseudo-code. Obviously in real code, you would almost always be better off using a real database (e.g. sqlite) with SQL.

# All these examples have been tested using SQL in Postgresql
# pseudo tables each with 2 cols (they do not have to have the same number of columns)
table1 = [[1, 'A'], [1, 'B'], [2, 'C'], [2, 'A'], [3, 'D'], [4, 'E'], [5, 'E']]
table2 = [[1, 'w'], [1, 'x'], [2, 'w'], [2, 'y'], [3, 'z'], [6, 's'], [7, 's']]
def do_join(join_type):
print('\n---------------------------------------------------------------')
print(join_type)
print('SELECT table1.col2, table2.col2 FROM table1 {} table2 ON table1.col1=table2.col1;'.format(join_type))
table1_used = set()
table2_used = set()
results = []
for id1, row1 in enumerate(table1):
for id2, row2 in enumerate(table2):
if row1[0] == row2[0]:
results.append([id1, id2] + row1[1:] + row2[1:])
table1_used.add(id1)
table2_used.add(id2)
if join_type in ['FULL JOIN', 'LEFT JOIN']:
none_cols = [None] * (len(table2[0]) - 1)
for id1, row1 in enumerate(table1):
if id1 not in table1_used:
results.append([id1, None] + row1[1:] + none_cols)
if join_type in ['FULL JOIN', 'RIGHT JOIN']:
none_cols = [None] * (len(table1[0]) - 1)
for id2, row2 in enumerate(table2):
if id2 not in table2_used:
results.append([None, id2] + none_cols + row2[1:])
if join_type == 'RIGHT JOIN':
results.sort(key=lambda x: x[1])
for r in results:
print(r[2:])
do_join('FULL JOIN')
do_join('LEFT JOIN')
do_join('RIGHT JOIN')
do_join('JOIN')
RESULTS
---------------------------------------------------------------
FULL JOIN
SELECT table1.col2, table2.col2 FROM table1 FULL JOIN table2 ON table1.col1=table2.col1;
['A', 'w']
['A', 'x']
['B', 'w']
['B', 'x']
['C', 'w']
['C', 'y']
['A', 'w']
['A', 'y']
['D', 'z']
['E', None]
['E', None]
[None, 's']
[None, 's']

---------------------------------------------------------------
LEFT JOIN
SELECT table1.col2, table2.col2 FROM table1 LEFT JOIN table2 ON table1.col1=table2.col1;
['A', 'w']
['A', 'x']
['B', 'w']
['B', 'x']
['C', 'w']
['C', 'y']
['A', 'w']
['A', 'y']
['D', 'z']
['E', None]
['E', None]

---------------------------------------------------------------
RIGHT JOIN
SELECT table1.col2, table2.col2 FROM table1 RIGHT JOIN table2 ON table1.col1=table2.col1;
['A', 'w']
['B', 'w']
['A', 'x']
['B', 'x']
['C', 'w']
['A', 'w']
['C', 'y']
['A', 'y']
['D', 'z']
[None, 's']
[None, 's']

---------------------------------------------------------------
JOIN
SELECT table1.col2, table2.col2 FROM table1 JOIN table2 ON table1.col1=table2.col1;
['A', 'w']
['A', 'x']
['B', 'w']
['B', 'x']
['C', 'w']
['C', 'y']
['A', 'w']
['A', 'y']
['D', 'z']

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK