Consultas de Selección: Cruce de Tablas, JOINs¶

Conexión a la Base de Datos¶

[1]:

import pandas as pd
import psycopg2
import sqlalchemy
import matplotlib as plt

%matplotlib inline

from sqlalchemy import create_engine

POSTGRES_ADDRESS = 'localhost' # Este es el servidor, puede ser una IP
POSTGRES_PORT = '5432'
POSTGRES_USERNAME = 'postgres'
POSTGRES_PASSWORD = '1234'
POSTGRES_DBNAME = 'dvdrental'

# Ahora se configura la cadena de conexión.
# Esta es una cadena que contiene los parámetros necesarios para establecer una
# conexión con posgres.
postgres_str = ('postgresql://{username}:{password}@{ipaddress}:{port}/{dbname}'.format(username=POSTGRES_USERNAME,
                                                                                        password=POSTGRES_PASSWORD,
                                                                                        ipaddress=POSTGRES_ADDRESS,
                                                                                        port=POSTGRES_PORT,
                                                                                        dbname=POSTGRES_DBNAME))
# A continuación se crea la conexión
cnx = create_engine(postgres_str)

Cruce de Tablas¶

Existen varios tipos de Joins (cruce, unión o combinación) en SQL. En PostgreSQL, se implementa la unión interna (inner join), la unión izquierda (left join), la unión derecha (right join) y la unión externa completa (full outer join).

La unión de PostgreSQL se utiliza para combinar columnas de una ( autounión - self join ) o más tablas según los valores de las columnas comunes entre tablas relacionadas. Las columnas comunes suelen ser las columnas de clave principal de la primera tabla (origen) y las columnas de clave externa o foránea de la segunda tabla (destino).

[2]:

#Suponga que tenemos dos canastas
sql = '''
DROP TABLE IF EXISTS canastaX;
CREATE TABLE canastaX (
    x INT PRIMARY KEY,
    frutaX VARCHAR (100) NOT NULL
);

DROP TABLE IF EXISTS canastaY;
CREATE TABLE canastaY (
    y INT PRIMARY KEY,
    frutaY VARCHAR (100) NOT NULL
);

INSERT INTO canastaX (x, frutaX)
VALUES
    (1, 'Manzana'),
    (2, 'Naranja'),
    (3, 'Banano'),
    (4, 'Uva');

INSERT INTO canastaY (y, frutaY)
VALUES
    (1, 'Naranja'),
    (2, 'Manzana'),
    (3, 'Melón'),
    (4, 'Pera');

'''

cnx.execute(sql)

[2]:

<sqlalchemy.engine.result.ResultProxy at 0x2c1c38eccd0>

[3]:

# Verificamos que las tablas tengan datos
sql = '''
SELECT * FROM canastaX;
'''
pd.read_sql_query(sql, cnx)

[3]:

	x	frutax
0	1	Manzana
1	2	Naranja
2	3	Banano
3	4	Uva

[4]:

# Verificamos que las tablas tengan datos
sql = '''
SELECT * FROM canastaY;
'''
pd.read_sql_query(sql, cnx)

[4]:

	y	frutay
0	1	Naranja
1	2	Manzana
2	3	Melón
3	4	Pera

Unión interna - Inner Join¶

La combinación interna examina cada fila de la primera tabla (canastaX), compara el valor de la columna frutaX con el valor de la columna frutaY de cada fila de la segunda tabla (canastaY). Si estos valores son iguales, la combinación interna crea una nueva fila que contiene columnas de ambas tablas y agrega esta nueva fila al conjunto de resultados. En teoría de conjuntos esta es la intersección de canastaX con canastaY.

[5]:

sql = '''
SELECT x, frutax, y, frutay
FROM canastaX
INNER JOIN canastaY
    ON frutax = frutay;
'''
pd.read_sql_query(sql, cnx)

[5]:

	x	frutax	y	frutay
0	1	Manzana	2	Manzana
1	2	Naranja	1	Naranja

[6]:

# Con la base de datos de alquiler de peliculas
sql = '''
SELECT
    customer.customer_id,
    first_name,
    last_name,
    amount,
    payment_date
FROM
    customer
INNER JOIN payment
    ON payment.customer_id = customer.customer_id
ORDER BY payment_date;
'''
pd.read_sql_query(sql, cnx)

[6]:

	customer_id	first_name	last_name	amount	payment_date
0	416	Jeffery	Pinson	2.99	2007-02-14 21:21:59.996577
1	516	Elmer	Noe	4.99	2007-02-14 21:23:39.996577
2	239	Minnie	Romero	4.99	2007-02-14 21:29:00.996577
3	592	Terrance	Roush	6.99	2007-02-14 21:41:12.996577
4	49	Joyce	Edwards	0.99	2007-02-14 21:44:52.996577
...	...	...	...	...	...
14591	163	Cathy	Spencer	0.00	2007-05-14 13:44:29.996577
14592	168	Regina	Berry	0.99	2007-05-14 13:44:29.996577
14593	175	Annette	Olson	3.98	2007-05-14 13:44:29.996577
14594	175	Annette	Olson	0.00	2007-05-14 13:44:29.996577
14595	178	Marion	Snyder	4.99	2007-05-14 13:44:29.996577

14596 rows × 5 columns

[7]:

# Dado que ambas tablas tienen la misma columna customer_id, puede usar la sintaxis USING.
sql = '''
SELECT
    customer_id,
    first_name,
    last_name,
    amount,
    payment_date
FROM
    customer
INNER JOIN payment USING(customer_id)
ORDER BY payment_date;
'''
pd.read_sql_query(sql, cnx)

[7]:

	customer_id	first_name	last_name	amount	payment_date
0	416	Jeffery	Pinson	2.99	2007-02-14 21:21:59.996577
1	516	Elmer	Noe	4.99	2007-02-14 21:23:39.996577
2	239	Minnie	Romero	4.99	2007-02-14 21:29:00.996577
3	592	Terrance	Roush	6.99	2007-02-14 21:41:12.996577
4	49	Joyce	Edwards	0.99	2007-02-14 21:44:52.996577
...	...	...	...	...	...
14591	163	Cathy	Spencer	0.00	2007-05-14 13:44:29.996577
14592	168	Regina	Berry	0.99	2007-05-14 13:44:29.996577
14593	175	Annette	Olson	3.98	2007-05-14 13:44:29.996577
14594	175	Annette	Olson	0.00	2007-05-14 13:44:29.996577
14595	178	Marion	Snyder	4.99	2007-05-14 13:44:29.996577

14596 rows × 5 columns

[8]:

# Dado que ambas tablas tienen la misma columna customer_id, puede usar la sintaxis USING.
sql = '''
SELECT
    c.customer_id,
    c.first_name customer_first_name,
    c.last_name customer_last_name,
    s.first_name staff_first_name,
    s.last_name staff_last_name,
    amount,
    payment_date
FROM
    customer c
INNER JOIN payment p
    ON p.customer_id = c.customer_id
INNER JOIN staff s
    ON p.staff_id = s.staff_id
ORDER BY payment_date;
'''
pd.read_sql_query(sql, cnx)

[8]:

	customer_id	customer_first_name	customer_last_name	staff_first_name	staff_last_name	amount	payment_date
0	416	Jeffery	Pinson	Jon	Stephens	2.99	2007-02-14 21:21:59.996577
1	516	Elmer	Noe	Jon	Stephens	4.99	2007-02-14 21:23:39.996577
2	239	Minnie	Romero	Mike	Hillyer	4.99	2007-02-14 21:29:00.996577
3	592	Terrance	Roush	Jon	Stephens	6.99	2007-02-14 21:41:12.996577
4	49	Joyce	Edwards	Mike	Hillyer	0.99	2007-02-14 21:44:52.996577
...	...	...	...	...	...	...	...
14591	163	Cathy	Spencer	Mike	Hillyer	0.00	2007-05-14 13:44:29.996577
14592	168	Regina	Berry	Mike	Hillyer	0.99	2007-05-14 13:44:29.996577
14593	175	Annette	Olson	Jon	Stephens	3.98	2007-05-14 13:44:29.996577
14594	175	Annette	Olson	Jon	Stephens	0.00	2007-05-14 13:44:29.996577
14595	178	Marion	Snyder	Mike	Hillyer	4.99	2007-05-14 13:44:29.996577

14596 rows × 7 columns

Unión a la izquierda - Left Join¶

La siguiente consulta usa la cláusula de combinación izquierda para unir la tabla canastaX con la canastaY. En el contexto de unión a la izquierda, la primera tabla se denomina tabla izquierda y la segunda tabla se denomina tabla derecha.

Se mapean todos los elementos de la tabla izquierda con los que se puedan de la tabla derecha, el resto se mapea con NULL.

El siguiente diagrama de Venn ilustra la unión izquierda:

Fuente de la imagen.

Nota: LEFT JOIN es el mismo que LEFT OUTER JOIN, puede usarlos indistintamente.

[9]:

sql = '''
SELECT x, frutaX, y, frutaY
FROM canastaX
LEFT JOIN canastaY
    ON frutaX = frutaY;
'''

pd.read_sql_query(sql, cnx)

[9]:

	x	frutax	y	frutay
0	1	Manzana	2.0	Manzana
1	2	Naranja	1.0	Naranja
2	3	Banano	NaN	None
3	4	Uva	NaN	None

[10]:

# Películas que están en inventario
sql = '''
SELECT
    film.film_id,
    title,
    inventory_id
FROM
    film
LEFT JOIN inventory
    ON inventory.film_id = film.film_id
WHERE film.film_id in (13,14,15)
ORDER BY title;
'''

pd.read_sql_query(sql, cnx)

[10]:

	film_id	title	inventory_id
0	13	Ali Forever	67.0
1	13	Ali Forever	68.0
2	13	Ali Forever	69.0
3	13	Ali Forever	70.0
4	14	Alice Fantasia	NaN
5	15	Alien Center	72.0
6	15	Alien Center	73.0
7	15	Alien Center	74.0
8	15	Alien Center	75.0
9	15	Alien Center	76.0
10	15	Alien Center	71.0

[11]:

# Películas que NO están en inventario
sql = '''
SELECT
    f.film_id,
    title,
    inventory_id
FROM
    film f
LEFT JOIN inventory i USING (film_id)
WHERE i.film_id IS NULL
ORDER BY title;
'''

pd.read_sql_query(sql, cnx)

[11]:

	film_id	title	inventory_id
0	14	Alice Fantasia	None
1	33	Apollo Teen	None
2	36	Argonauts Town	None
3	38	Ark Ridgemont	None
4	41	Arsenic Independence	None
5	87	Boondock Ballroom	None
6	108	Butch Panther	None
7	128	Catch Amistad	None
8	144	Chinatown Gladiator	None
9	148	Chocolate Duck	None
10	171	Commandments Express	None
11	192	Crossing Divorce	None
12	195	Crowds Telemark	None
13	198	Crystal Breaking	None
14	217	Dazed Punk	None
15	221	Deliverance Mulholland	None
16	318	Firehouse Vietnam	None
17	325	Floats Garden	None
18	332	Frankenstein Stranger	None
19	359	Gladiator Westward	None
20	386	Gump Date	None
21	404	Hate Handicap	None
22	419	Hocus Frida	None
23	495	Kentuckian Giant	None
24	497	Kill Brotherhood	None
25	607	Muppet Mile	None
26	642	Order Betrayed	None
27	669	Pearl Destiny	None
28	671	Perdition Fargo	None
29	701	Psycho Shrunk	None
30	712	Raiders Antitrust	None
31	713	Rainbow Shock	None
32	742	Roof Champion	None
33	801	Sister Freddy	None
34	802	Sky Miracle	None
35	860	Suicides Silence	None
36	874	Tadpole Park	None
37	909	Treasure Command	None
38	943	Villain Desperate	None
39	950	Volume House	None
40	954	Wake Jaws	None
41	955	Walls Artist	None

Unión a la derecha - Right Join¶

Es una versión inversa de la combinación izquierda. La combinación de la derecha comienza a realizar el mapeo de derecha a izquierda.

[12]:

sql = '''
SELECT x, frutaX, y, frutaY
FROM canastaX
RIGTH  JOIN canastaY
    ON frutaX = frutaY;
'''

pd.read_sql_query(sql, cnx)

[12]:

	x	frutax	y	frutay
0	1	Manzana	2	Manzana
1	2	Naranja	1	Naranja

Unión Externa Completa¶

La combinación externa completa o la combinación completa devuelve un conjunto de resultados que contiene todas las filas de las tablas izquierda y derecha, con las filas coincidentes de ambos lados si están disponibles. En caso de que no haya coincidencias, las columnas de la tabla se llenarán con NULL.

Fuente

Más Ejemplos

[13]:

sql = '''
SELECT x, frutaX, y, frutaY
FROM canastaX
FULL OUTER JOIN canastaY
    ON frutaX = frutaY;
'''

pd.read_sql_query(sql, cnx)

[13]:

	x	frutax	y	frutay
0	1.0	Manzana	2.0	Manzana
1	2.0	Naranja	1.0	Naranja
2	3.0	Banano	NaN	None
3	4.0	Uva	NaN	None
4	NaN	None	4.0	Pera
5	NaN	None	3.0	Melón

El siguiente diagrama de Venn ilustra la combinación externa completa que devuelve filas de una tabla que no tienen las filas correspondientes en la otra tabla

Fuente

[14]:

# Para consultar las filas en una tabla que no tienen filas
# coincidentes en la otra, usa la combinación completa

sql = '''
SELECT x, frutaX, y, frutaY
FROM canastaX
FULL OUTER JOIN canastaY
    ON frutaX = frutaY
WHERE x IS NULL OR y IS NULL;
'''

pd.read_sql_query(sql, cnx)

[14]:

	x	frutax	y	frutay
0	3.0	Banano	NaN	None
1	4.0	Uva	NaN	None
2	NaN	None	4.0	Pera
3	NaN	None	3.0	Melón

Autocombinación¶

Cuando unes una tabla a sí misma (también conocida como autounión), necesitas usar alias de tabla. En el siguiente ejemplo un empleado puede ser el supervisor, gerente o administrador de otro.

[16]:

sql = '''
DROP TABLE IF EXISTS employee;

CREATE TABLE employee (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR (255) NOT NULL,
    last_name VARCHAR (255) NOT NULL,
    manager_id INT,
    FOREIGN KEY (manager_id)
    REFERENCES employee (employee_id)
    ON DELETE CASCADE
);

INSERT INTO employee (
    employee_id,
    first_name,
    last_name,
    manager_id
)
VALUES
    (1, 'Windy', 'Hays', NULL),
    (2, 'Ava', 'Christensen', 1),
    (3, 'Hassan', 'Conner', 1),
    (4, 'Anna', 'Reeves', 2),
    (5, 'Sau', 'Norman', 2),
    (6, 'Kelsie', 'Hays', 3),
    (7, 'Tory', 'Goff', 3),
    (8, 'Salley', 'Lester', 3);
'''

cnx.execute(sql)

[16]:

<sqlalchemy.engine.result.ResultProxy at 0x2c1c38f4c10>

En la tabla employee, la columna manager_id hace referencia a la columna employee_id. El valor en la columna manager_id muestra el gerente al que el empleado reporta directamente. Cuando el valor de la columna manager_id es nulo, ese empleado no informa a nadie. En otras palabras, él o ella es el gerente superior.

[17]:

sql = '''
SELECT
    e.first_name || ' ' || e.last_name employee,
    m .first_name || ' ' || m .last_name manager
FROM
    employee e
INNER JOIN employee m ON m .employee_id = e.manager_id
ORDER BY manager;
'''

pd.read_sql_query(sql, cnx)

[17]:

	employee	manager
0	Sau Norman	Ava Christensen
1	Anna Reeves	Ava Christensen
2	Salley Lester	Hassan Conner
3	Kelsie Hays	Hassan Conner
4	Tory Goff	Hassan Conner
5	Ava Christensen	Windy Hays
6	Hassan Conner	Windy Hays

[18]:

sql = '''
DROP TABLE employee;
'''

cnx.execute(sql)

[18]:

<sqlalchemy.engine.result.ResultProxy at 0x2c1c0692dc0>

Cláusula CROSS JOIN¶

Permite producir un Producto cartesiano de filas en dos o más tablas.

Si T1 tiene n filas y T2 tiene m filas, el conjunto de resultados tendrá nxm filas. Por ejemplo, T1 tiene 100 filas y T2 tiene 100 filas, el conjunto de resultados tendrá 100 x 100= 10000 filas.

[19]:

sql = '''
DROP TABLE IF EXISTS T1;
CREATE TABLE T1 (label CHAR(1) PRIMARY KEY);

DROP TABLE IF EXISTS T2;
CREATE TABLE T2 (score INT PRIMARY KEY);

INSERT INTO T1 (label)
VALUES
    ('A'),
    ('B');

INSERT INTO T2 (score)
VALUES
    (1),
    (2),
    (3);
'''

cnx.execute(sql)

[19]:

<sqlalchemy.engine.result.ResultProxy at 0x2c1c3f9d730>

[20]:

sql = '''
SELECT *
FROM T1
CROSS JOIN T2;
'''

pd.read_sql_query(sql, cnx)

[20]:

	label	score
0	A	1
1	B	1
2	A	2
3	B	2
4	A	3
5	B	3

[21]:

# Eliminar las tablas del ejemplo
sql = '''
DROP TABLE IF EXISTS T1;
DROP TABLE IF EXISTS T2;
'''

cnx.execute(sql)

[21]:

<sqlalchemy.engine.result.ResultProxy at 0x2c1c3912d60>

Cláusula NATURAL JOIN¶

Una combinación natural es una combinación que crea una combinación implícita basada en los mismos nombres de columna en las tablas unidas.

[22]:

# Cada categoría tiene cero o muchos productos y cada producto pertenece a una y solo una categoría.

sql = '''
DROP TABLE IF EXISTS categories;
CREATE TABLE categories (
    category_id serial PRIMARY KEY,
    category_name VARCHAR (255) NOT NULL
);

DROP TABLE IF EXISTS products;
CREATE TABLE products (
    product_id serial PRIMARY KEY,
    product_name VARCHAR (255) NOT NULL,
    category_id INT NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories (category_id)
);
'''

cnx.execute(sql)

[22]:

<sqlalchemy.engine.result.ResultProxy at 0x2c1c3ee9bb0>

[23]:

sql = '''
INSERT INTO categories (category_name)
VALUES
    ('Smart Phone'),
    ('Laptop'),
    ('Tablet');

INSERT INTO products (product_name, category_id)
VALUES
    ('iPhone', 1),
    ('Samsung Galaxy', 1),
    ('HP Elite', 2),
    ('Lenovo Thinkpad', 2),
    ('iPad', 3),
    ('Kindle Fire', 3);
'''

cnx.execute(sql)

[23]:

<sqlalchemy.engine.result.ResultProxy at 0x2c1c3ee9e80>

[24]:

# NATURAL JOIN
sql = '''
SELECT * FROM products
NATURAL JOIN categories;
'''

pd.read_sql_query(sql, cnx)

[24]:

	category_id	product_id	product_name	category_name
0	1	1	iPhone	Smart Phone
1	1	2	Samsung Galaxy	Smart Phone
2	2	3	HP Elite	Laptop
3	2	4	Lenovo Thinkpad	Laptop
4	3	5	iPad	Tablet
5	3	6	Kindle Fire	Tablet

[25]:

# Equivalente al NATURAL JOIN
sql = '''
SELECT * FROM products
INNER JOIN categories USING (category_id);
'''

pd.read_sql_query(sql, cnx)

[25]:

	category_id	product_id	product_name	category_name
0	1	1	iPhone	Smart Phone
1	1	2	Samsung Galaxy	Smart Phone
2	2	3	HP Elite	Laptop
3	2	4	Lenovo Thinkpad	Laptop
4	3	5	iPad	Tablet
5	3	6	Kindle Fire	Tablet

[26]:

sql = '''
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
'''

cnx.execute(sql)

[26]:

<sqlalchemy.engine.result.ResultProxy at 0x2c1c3ee9760>