Join the correct tables

Applying SQL to Real-World Problems

Dmitriy (Dima) Gorenshteyn

Lead Data Scientist, Memorial Sloan Kettering Cancer Center

All tables & columns

PostgreSQL:

SELECT * FROM information_schema.columns;

SQL Server - TSQL:

SELECT * FROM information_schema.columns;

MySQL:

SELECT * FROM information_schema.columns;

...

All tables & columns

PostgreSQL:

SELECT * 
FROM information_schema.columns
;

table_catalog    table_schema    table_name   column_name
pagilla          pg_catalog      pg_proc      proname    
pagilla          pg_catalog      pg_proc      pronamespace    
pagilla          pg_catalog      pg_proc      proowner    
pagilla          pg_catalog      pg_proc      prolang    
...              ...             ...          ...

All tables & columns

PostgreSQL:

SELECT * 
FROM information_schema.columns
WHERE table_schema = 'public';

table_catalog    table_schema    table_name   column_name
pagilla          public          address      address_id    
pagilla          public          address      address    
pagilla          public          address      district    
pagilla          public          address      city        
...              ...             ...          ...

Aggregate the columns

SELECT table_name, 
       STRING_AGG(column_name, ', ') AS columns


;

Aggregate the columns

SELECT table_name, 
       STRING_AGG(column_name, ', ') AS columns
FROM information_schema.columns

;

Aggregate the columns

SELECT table_name, 
       STRING_AGG(column_name, ', ') AS columns
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name;

table          columns
rental         rental_id, rental_date, inventory_id, customer_id, return_date    
film_actor     actor_id, film_id    
film           film_id, title, description, release_year, language_id, ...
customer       customer_id, first_name, last_name, email, address_id, active    
...            ...

A VIEW of tables and columns

A VIEW is a virtual table.

CREATE VIEW name_of_view AS
...

CREATE VIEW table_columns AS
SELECT table_name, 
       STRING_AGG(column_name, ', ') AS columns
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name;

table_columns

SELECT * 
FROM table_columns;

table          columns
rental         rental_id, rental_date, inventory_id, customer_id, return_date    
film_actor     actor_id, film_id    
film           film_id, title, description, release_year, language_id, rental_duration, rental_rate, length, replacement_cost, rating, special_features    
customer       customer_id, first_name, last_name, email, address_id, active    
actor          actor_id, first_name, last_name    
...            ...

Let's find some data!

Applying SQL to Real-World Problems