您在這裡

Using JOIN to retrieve data

24 二月, 2015 - 12:54

Now that we have followed the rules of database normalization and have data separated into two tables, linked together using primary and foreign keys, we need to be able to build a SELECT that re-assembles the data across the tables.

SQL uses the JOIN clause to re-connect these tables. In the JOIN clause you specify the fields that are used to re-connect the rows between the tables.

The following is an example of a SELECT with a JOIN clause:

SELECT * FROM Follows JOIN People    ON Follows.from_id = People.id WHERE People.id = 1

The JOIN clause indicates that the fields we are selecting cross both the Follows and People tables. The ON clause indicates how the two tables are to be joined. Take the rows from Follows and append the row from People where the field from_id in Follows is the same the id value in the People table.

media/image20.png

The result of the JOIN is to create extra-long “meta-rows” which have both the fields from People and the matching fields from Follows. Where there is more than one match between the id field from People and the from_id from People, then JOIN creates a meta-row for each of the matching pairs of rows, duplicating data as needed.

The following code demonstrates the data that we will have in the database after the multi-table Twitter spider program (above) has been run several times.

import sqlite3

conn = sqlite3.connect('spider.sqlite3')cur = conn.cursor()

cur.execute('SELECT * FROM People')count = 0print 'People:'for row in cur :    if count < 5: print row    count = count + 1print count, 'rows.'

cur.execute('SELECT * FROM Follows')count = 0print 'Follows:'for row in cur :    if count < 5: print row    count = count + 1print count, 'rows.'

cur.execute('''SELECT * FROM Follows JOIN People    ON Follows.from_id = People.id WHERE People.id = 2''')count = 0print 'Connections for id=2:'for row in cur :    if count < 5: print row    count = count + 1print count, 'rows.'

cur.close()

In this program, we first dump out the People and Follows and then dump out a subset of the data in the tables joined together.

Here is the output of the program:

python twjoin.pyPeople:(1, u'drchuck', 1)(2, u'opencontent', 1)(3, u'lhawthorn', 1)(4, u'steve_coppin', 0)(5, u'davidkocher', 0)55 rows.Follows:(1, 2)(1, 3)(1, 4)(1, 5)(1, 6)60 rows.Connections for id=2:(2, 1, 1, u'drchuck', 1)(2, 28, 28, u'cnxorg', 0)(2, 30, 30, u'kthanos', 0)(2, 102, 102, u'SomethingGirl', 0)(2, 103, 103, u'ja_Pac', 0)20 rows.

You see the columns from the People and Follows tables and the last set of rows is the result of the SELECT with the JOIN clause.

In the last select, we are looking for accounts that are friends of “opencontent” (i.e. People.id=2).

In each of the “meta-rows” in the last select, the first two columns are from the Follows table followed by columns three through five from the People table. You can also see that the second column (Follows.to_id) matches the third column (People.id) in each of the joined-up “meta-rows”.