Aún no tienes acceso a esta clase

Crea una cuenta y continúa viendo este curso

Curso de PostgreSQL

Curso de PostgreSQL

Oswaldo Rodríguez González

Oswaldo Rodríguez González

Cruzar tablas: SQL JOIN

18/32
Recursos

Aportes 72

Preguntas 9

Ordenar por:

¿Quieres ver más aportes, preguntas y respuestas de la comunidad? Crea una cuenta o inicia sesión.

Tipos de Join

--------------------------RESUMEN----------------------------------------
/* Inner join: solo nos trae los datos que coinciden en ambas tablas. */
select * from route r
inner join train tr
on tr.train_id = r.train_id;

/* Este es un full outer join: trae todos los datos de ambas tablas. Coincidan o no. */
select * 
from route r
full outer join train tr
on tr.train_id = r.train_id;


/*Este full outer join ahora solo nos trae los datos que no coinciden de la tabla A 
 con la la tabla B, tambien nos trae los datos de la tabla B que no coiniden con la
 tabla A. (es como el opuesto del inner join, porque en lugar de traernos los que 
 coinciden en ambas tablas, nos trae solo los que no coinciden en ambas tablas.)*/
select * 
from route r
full outer join train tr
on tr.train_id = r.train_id
where r.train_id is null
or tr.train_id is null;


/* left join: nos trae todos los datos de la tabla A(izquierda) y solo los datos de la
 tabla B que coincidan en la tabla A. */
select * from route r
left join train tr
on tr.train_id = r.train_id;

/* left outer join: nos devuelve todos los datos de la tabla A que no coincide con la
tabla B. */
select * from route r
left join train tr
on tr.train_id = r.train_id
where tr.train_id is null;

/*right join: nos devuelve todos los datos de la tabla B, y solo los datos de la tabla
A que coincidan con la tabla B*/
select * from route r
right join train tr
on tr.train_id = r.train_id;

/* right outer join: nos trae todos los datos de la tabla B que no coinciden con
la tabla A*/

select * from route r
right join train tr
on tr.train_id = r.train_id
where r.train_id is null;

/*NOTA: Cuando usamos left join (Tabla A a la tabla B) estamos usando la tabla A, es
decir traemos todos los datos de la tabla A y solo los datos de la tabla B que coinciden
con la tabla A. si queremos usar un left outer join la llave primaria null que debemos
especificar es la de la tabla B.
WHERE b.pkey us null;
lo mismo pasa cuando usamos un right outer join
como usamos la tabla B y solo los que coinciden con la tabla A entonces la llave null
que usamos es de la tabla A.
WHERE a.pkey us null;								*/



Comparto a la comunidad mis notas de este curso en un repositorio, espero les sean de mucha utilidad, recuerden hacer buen uso

https://github.com/rb-one/Curso_PostgreSQL/blob/master/Notes/notes.md

El mejor resumen

https://www.postgresqltutorial.com/wp-content/uploads/2018/12/PostgreSQL-Joins.png

Consultas

SELECT * FROM pasajero
INNER JOIN viaje ON (viaje.id_pasajero = pasajero.id);

SELECT * FROM pasajero
LEFT JOIN viaje ON (viaje.id_pasajero = pasajero.id)
WHERE viaje.id IS NULL;
SELECT 
pasajero.nombre AS pasajero,
tren.modelo AS tren,  
trayecto.nombre AS trayecto, 
estacion.nombre AS estacion,
viaje.inicio, 
viaje.fin
FROM pasajero
INNER JOIN viaje
ON viaje.id_pasajero = pasajero.id
INNER JOIN trayecto
ON viaje.id_trayecto = trayecto.id
INNER JOIN tren
ON trayecto.id_tren = tren.id
INNER JOIN estacion
ON trayecto.id_estacion = estacion.id;

Left Join funciona de manera optima en la generación de reportes cuando se buscan bugs o errores en la inserción de datos

En la versión 12 de postgreSQL se usan comillas dobles ( " " ) para los campos, así funciona correctamente el join
SELECT
*
FROM
pasajero
join
viaje
ON
(viaje.“Id_pasajero”=pasajero.“Id”)

Estas son las consultas que hice:

En esta página hay ejemplos interactivos para todos los casos de los SQL Join.
https://www.w3schools.com/sql/sql_join.asp

La primer consulta me sale totalmente vacía. En algún momento llenamos la tabla “viaje” y me lo saltee?

Ejemplo de una de las consultas que realice:

El rigth join con trayecto y tren
SELECT
*
FROM
trayecto
RIGHT JOIN
tren
ON
(trayecto.“Id_tren”=tren.“Id”);

– FULL OUTER JOIN
SELECT
*
FROM
trayecto
FULL OUTER JOIN
tren
ON
(trayecto.“Id_tren”=tren.“Id”);

ESTACION Y TRAYECTO
–Full outer join
SELECT
*
FROM
trayecto
FULL OUTER JOIN
estacion
ON
(trayecto.“Id_estacion”=estacion.“Id”);
–RIGHT JOIN
SELECT
*
FROM
trayecto
RIGHT JOIN
estacion
ON
(trayecto.“Id_estacion”=estacion.“Id”);

Comparto un join de las tablas de estacion, tren y trayecto
SELECT
trayecto.“Id” as IdTrayecto,
trayecto.“Nombre” as Nombre,
tren.“Modelo” as tren,
tren.“Capacidad” as Capacidad,
estacion.“Nombre” as estacion,
estacion.“Direccion” as ubicacion
FROM
trayecto
JOIN
tren
ON
(trayecto.“Id_tren”=tren.“Id”)
JOIN
estacion
ON
(trayecto.“Id_estacion” = estacion.“Id”)

Los datos se pueden cruzar utilizando JOIN y sus diferentes tipos, los cuales se basan en el uso de teoría de conjuntos para analizar y/o dar manejo a los datos, de mi banco de datos, dependiendo de lo que se quiera analizar.

Para poder realizarlo le incluí las comillas

<SELECT * FROM "Historica"
JOIN "Docentes" ON "Docentes"."DOCUMENTO" = "Historica"."DOCUMENTO";> 

SQL JOINS - Imagen del vídeo

Aquí hice los 7 cruces de tablas posibles con las tablas pasajeros y viajes y las preguntas que podrían responder:

**¿A qué horas iniciaron y finalizaron su viaje los pasajeros? **

SELECT *
FROM pasajeros
LEFT JOIN viajes
ON pasajeros.id = viajes.id_pasajeros;

¿Qué personas están registradas pero no han hecho ningún viaje?

SELECT *
FROM pasajeros
LEFT JOIN viajes
ON pasajeros.id = viajes.id_pasajeros
WHERE viajes.id_pasajeros IS NULL;

¿Cuántos viajes efectivos han sido realizados por los pasajeros?

SELECT *
FROM pasajeros
RIGHT JOIN viajes
ON pasajeros.id = viajes.id_pasajeros;

¿Qué viajes o cuántos viajes han tomado los pasajeros que no estén registrados?

SELECT *
FROM pasajeros
RIGHT JOIN viajes
ON pasajeros.id = viajes.id_pasajeros
WHERE pasajeros.id IS NULL;

¿Cuántos pasajeros han tomados viajes de forma efectiva?

SELECT *
FROM pasajeros
INNER JOIN viajes
ON pasajeros.id = viajes.id_pasajeros;

Bueno, aquí se unifican las tablas pasajeros y viajes

SELECT *
FROM pasajeros
FULL OUTER JOIN viajes
ON pasajeros.id = viajes.id_pasajeros;

¿Cuántos pasajeros no han hecho viajes?

SELECT *
FROM pasajeros
LEFT JOIN viajes
ON pasajeros.id = viajes.id_pasajeros
WHERE pasajeros.id IS NULL
OR viajes.id_pasajeros IS NULL;

Aquí un Inner Join entre trayecto, estacion y tren.
Muestra las estaciones, el trayecto y el tren que hace el trayecto.

select * from trayectos as t
inner join viajes as v on v.id_trayecto= t.id;

Información resumida de esta clase
#EstudiantesDePlatzi

  • Los JOIN es la teoría de conjuntos implementada en SQL

  • Los JOIN nos permite crear intersección, consultar el conjunto de la izquierda, derecha o completo

  • Es de gran ayuda pegar el papelito de JOIN cerca al lugar de trabajo

estupendo. me encanto la forma de hacer data ejemplo cool

Algunas de mis consultas combinadas…

SELECT * FROM "TREN"
LEFT JOIN "TRAYECTO" ON ("TREN".id = "TRAYECTO".id_tren)
ORDER BY "TREN".id;

SELECT * FROM "ESTACION"
INNER JOIN "TRAYECTO" ON ("ESTACION".id = "TRAYECTO".id_estacion)
ORDER BY "ESTACION".id;

Los join son una maravilla y super simples de entender

Hice una demostración uniendo 3 tablas, estacion,tren y trayecto ya que trayecto esta relacionada a ambas tablas, y le pedi que me mostraran los que tuvieran un nombre similar a mazda

Lo hice con este query

SELECT trayecto.id,trayecto.nombre AS nombre_trayecto,trenes.modelo,estaciones.nombre AS estaciones
FROM public.trayectos AS trayecto
INNER JOIN public.trenes ON trenes.id=trayecto.id_tren
INNER JOIN public.estaciones ON estaciones.id=trayecto.id_estacion
WHERE trenes.modelo LIKE '%Mazda%'
ORDER BY trayecto.nombre;

por alguna razon la sintaxis de mi pdadmin es bastante deferente a la que usa el profesor como muestro en la imagen. Si alguno sabe como cambiar esa sintaxis agradeceria me dejara saber como hacerlo. tardo mucho tiempo de descifrar que necesita el programa.

Les comparto algunas consultas interesantes del proyecto que estoy trabajando.
Aún me hace falta cargar mucho mas información y planear trayectos de pasajeros que hagan trasbordos, pero de momento así se van viendo las consultas que ya me sirven para reportar situaciones dentro de mi esquema.
Traté de compartir los recursos aqui, pero me pusieron el comentario en revision. Dejo la url de github en donde tengo ese script definido.

Saludos!

No es basicamente toda la teoría de conjuntos. Es algebra de conjuntos. Una parte de la gran teoría de conjuntos.

Nunca pude entender los joins. Espero esta vez tenga más suerte.

Jajaja obviamente el pasajero con Id 101 no había hecho viajes.

comparto mis query, espero que sirvan de ayuda:

-- Pasajeros que viajaron
SELECT * FROM "Pasajeros"
JOIN "Viajes" ON ("Viajes".id_pasajero = "Pasajeros".id);

--Pasajeros que no han viajado
SELECT * FROM "Pasajeros"
LEFT JOIN "Viajes" ON ("Viajes".id_pasajero = "Pasajeros".id)
WHERE "Viajes".id IS NULL;
SELECT 	tra."Nombre", 
		tre."Modelo", 
		tre."Capacidad", 
		est."Nombre"
FROM "Trayecto" tra
INNER JOIN "Tren" tre ON tra."IdTren" = tre."Id"
INNER JOIN "Estacion" est ON tra."IdEstacion" = est."Id"

Consulta para conocer los trayectos más visitados, el nombre de mis tablas está en plural por lo que tal vez necesiten cambiarlo.

select trayectos.nombre_trayecto, count(*) from trayectos
left join viajes on (viajes.id_trayecto = trayectos.id_trayecto)
where id_viaje is not null
group by trayectos.nombre_trayecto
order by count DESC;
SELECT *FROM tren
RIGHT JOIN trayecto ON (trayecto.id_tren = tren.id);```
SELECT pasajero.id, pasajero.nombre, COUNT(viaje.id) AS num_viajes FROM pasajero
LEFT JOIN viaje ON (viaje.id_pasajero = pasajero.id)
GROUP BY (pasajero.id)
HAVING COUNT(viaje.id) > 2
ORDER BY num_viajes DESC;

MUY BIEN EXPLICADO GRACIAS AQUÍ DEJO MI APORTE CON EL INNER JOIN

-- Para ver los pasajeros que hicieron un viaje
SELECT * FROM pasajero
JOIN viaje ON (viaje.id_pasajero = pasajero.id);

-- Para ver los pasajeros que no hicieron un viaje
SELECT * FROM pasajero
LEFT JOIN viaje ON (viaje.id_pasajero = pasajero.id)
WHERE viaje.id IS NULL;
<SELECT nombre, AGE(CURRENT_DATE, fecha_nacimiento) as edad, fecha_nacimiento
FROM pasajeros as p
LEFT JOIN viajes as v on v.pasajero_id = p.pasajero_id
WHERE viaje_id is NULL;>

con el pgadmin es realmente sencillo hacer las consultas

La unión de dos consultas para traer todos los datos, tengan o no relación

Hola!

No se si mi descripción del query esta correcta:

– cuantos viajes hizo un pasajero
SELECT pasajero.id, nombre, COUNT(pasajero.id)
FROM pasajero
JOIN viaje ON (viaje.id_pasajero = pasajero.id)
GROUP BY pasajero.ID;

– trae todos los trayectos que tienen tren
SELECT * FROM tren
RIGHT JOIN trayecto ON (trayecto.id_tren = tren.id)
WHERE tren.id is not null

– Trae todos los trayectos y trenes
SELECT * FROM tren
FULL JOIN trayecto ON (trayecto.id_tren = tren.id)

–Trae todas las estaciones que no tienen trayecto
SELECT * FROM trayecto
RIGHT JOIN estacion ON (trayecto.id_estacion = estacion.id)
WHERE trayecto.id IS NULL

inner join intersección

left join

right join

outer join

Aqui un JOIN simple para ver a los pasajeros cuando iniciaron el viaje y cuando termino

SELECT p.nombre, v.inicio, v.fin FROM pasajero as p
JOIN viaje as v
ON (v.id_pasajero = p.id);

Ejecutando Join

Los Joins son básicamente toda la teoría de conjunto aplicada a SQL

Tipos de JOINS: 1.INNER JOINS, 2.LEFT JOINS, 3.RIGHT JOINS, FULL OUTHER

Joins son toda las teorias de conjuntos implementadas en los joins

--¿Cantidad de viajes por estación?
SELECT 
	e.id, e.nombre, e.direccion, count (v.id_trayecto) AS cantidad_llegadas FROM estacion AS e
INNER JOIN  trayecto AS t
	ON (e.id = t.id_estacion)
INNER JOIN viaje AS v 
	ON(v.id_trayecto = t.id)
GROUP BY e.id
ORDER BY cantidad_llegadas DESC
;

--¿Cuáles usuarios hicieron  viajes?
SELECT * FROM pasajero
INNER JOIN viaje  
	ON (viaje.id_pasajero =pasajero.id);


--¿Cuáles fueron los  usuarios  no  realizaron viaje?
SELECT * FROM pasajero
LEFT JOIN viaje
	ON (viaje.id_pasajero = pasajero.id)
WHERE viaje.id_pasajero IS NULL

;

--¿Cuáles  son los trenes (Id ,modelo, capacidad )que no tienen trayecto asignado?
SELECT * FROM tren
LEFT JOIN  trayecto 
	ON (trayecto.id_tren = tren.id)
WHERE trayecto.id_tren IS NULL
;

SELECT * FROM viaje
WHERE EXTRACT (YEAR FROM viaje.inicio) ='2019';

SELECT * FROM viaje
WHERE  viaje.inicio >'2019-01-1' AND  viaje.inicio <'2019-01-31';


--¿Cuáles son usauarios viajaron  en el año 2019 y a que estación llegaron?
SELECT 
	p.id AS identificador, p.nombre AS nombre_pasajero, 
	v.inicio AS fecha_viaje, e.nombre  AS nombre_estacion  
FROM viaje AS  v
INNER JOIN	pasajero AS p 
	ON ( p.id = v.id_pasajero  )
INNER JOIN trayecto AS t 
	ON  (t.id = v.id_trayecto)
INNER JOIN estacion AS e
	ON (e.id = t.id_estacion)
WHERE EXTRACT (YEAR FROM v.inicio) ='2019';

;
	
--¿Cuáles son usauarios tienen reserva   entre la fecha 2019-01-01 y 2019-01-31 y a que estación llegaron?	
SELECT 
	p.id AS identificador, p.nombre AS nombre_pasajero, 
	v.inicio AS fecha_viaje, e.nombre  AS nombre_estacion
FROM viaje AS  v
INNER JOIN	pasajero AS p 
	ON ( p.id = v.id_pasajero  )
INNER JOIN trayecto AS t 
	ON  (t.id = v.id_trayecto)
INNER JOIN estacion AS e
	ON (e.id = t.id_estacion)
WHERE  v.inicio >'2019-01-01' AND  v.inicio <'2019-01-31'
	
;
//Estaciones en trayectos
SELECT * FROM estacion
JOIN trayecto On (trayecto.id_estacion = estacion.id)

//Trenes en trayectos
SELECT * FROM tren
JOIN  trayecto On (trayecto.id_tren = tren.id)



😄

SELECT * FROM trenes
RIGHT JOIN trayectos ON (trayectos.id_tren = trenes.id)

Con un Right Join sencillo me trae todos los trayectos y sus coincidencias de trenes. Acá no aparece ningún trayecto sin tren porque por la dependencia no pueden existir trayectos sin tren.

Aquí va mi aporte con un RIGHT JOIN y un OUTER EXCLUDING JOIN:

Tipos de Join

Ejemplo con INNER JOIN

Ejemplo con LEFT JOIN

Intenté hacer otra que es de que pasajeros viajan en cada tren, pero tengo que investigar más de ella debido a que tren y pasajeros no se relacionan directamente en una tabla.

-- En que estacion pasa cada tren
select estacion.nombre, tren.modelo
from trayecto
join estacion on (estacion.id_estacion = trayecto.id_estacion)
join tren on (tren.id = trayecto.id_tren);

Algunas consultas que se me ocurrieron:

-- Pasajeros que han tomado mas de 3 viajes
SELECT pa.nombre, COUNT(*) AS cantidad_viajes
FROM pasajero AS pa
	JOIN viaje AS vi ON (vi.id_pasajero = pa.id)
GROUP BY pa.id
HAVING COUNT(*) >= 3
ORDER BY cantidad_viajes DESC;

-- Trenes que tienen mas de un trayecto
SELECT 
	tr.id,
	tr.modelo,
	COUNT(*) AS cantidad_trayectos
FROM tren AS tr
	JOIN trayecto ON (trayecto.id_tren = tr.id)
GROUP BY tr.id
HAVING COUNT(*) >= 3
ORDER BY cantidad_trayectos DESC;

-- Los 5 trenes que tienen mas trayectos y sus trayectos separados por coma
SELECT 
	tr.id,
	tr.modelo,
	COUNT(*) AS cantidad_trayectos,
	string_agg(trayecto.nombre, ',') AS trayectos
FROM tren AS tr
	JOIN trayecto ON (trayecto.id_tren = tr.id)
GROUP BY tr.id
ORDER BY cantidad_trayectos DESC
LIMIT 5;

-- Los 5 pasajeros que han tomado mas viajes y nombre de las estaciones 
-- desde donde tomaron esos viajes separadas por '/'
SELECT
	pa.id,
	pa.nombre, 
	COUNT(*) AS cantidad_viajes,
	string_agg(es.nombre, '/') AS estaciones
FROM pasajero AS pa
	JOIN viaje AS vi ON (vi.id_pasajero = pa.id)
	JOIN trayecto AS tra ON (tra.id = vi.id_trayecto)
	JOIN estacion AS es ON (es.id = tra.id_estacion)
GROUP BY pa.id
ORDER BY cantidad_viajes DESC
--fetch first 5 rows only
LIMIT 5;

-- Idetificar trenes por capacidad
-- pequeno 0 pasajeros < capacidad <= 10 pasajeros
-- mediano 10 pasajeros < capacidad <= 50 pasajeros
-- grande 50 pasajeros < capacidad
SELECT 
	tre.id,
	tre.modelo,
	tre.capacidad,
	CASE
		WHEN tre.capacidad > 50 THEN 'Grande'
		WHEN tre.capacidad > 10 THEN 'Mediano'
		ELSE 'Pequeno'
	END AS "Clasificacion"
FROM tren AS tre;

-- Pasajeros que han tomado viajes en 2019 y 2020
-- nombre de la estacion desde donde tomaron el viaje
-- y la capacidad del tren que abordaron
SELECT
	pa.id,
	pa.nombre,
	vi.inicio AS inicio_viaje,
	es.nombre AS nombre_estacion,
	tre.capacidad AS capacidad_tren
FROM pasajero AS pa
	JOIN viaje AS vi ON (vi.id_pasajero = pa.id)
	JOIN trayecto AS tra ON (tra.id = vi.id_trayecto)
	JOIN estacion AS es ON (es.id = tra.id_estacion)
	JOIN tren AS tre ON (tre.id = tra.id_tren)
WHERE EXTRACT(YEAR FROM vi.inicio) >= '2019'
ORDER BY pa.id ASC;

join para ver cuantos pasajeros viajan en un tren

select count(v.id_pasajero) as Pasajeros, tr.id__tren as Tren
from trayecto as tr
join viaje as v
on (v.id_trayecto=tr.id)
group by tr.id__tren
order by tr.id__tren desc;

comprobando cuantos pasajeros viajan en el tren 98 para ver si coincide con el join realizado anterioremente

select v.id_pasajero,tr.id__tren
from viaje as v 
join trayecto as tr on (v.id_trayecto=tr.id)
where tr.id__tren = 98;

Select * from pasajero
right join viaje on pasajero.id= viaje.id_pasajero

Select * from trayecto
full outer join viaje on trayecto.id = viaje.id_trayecto

Mis querys

SELECT * FROM trayectos
JOIN estaciones ON (trayectos.id_estacion = trayectos.id)
JOIN trenes ON (trayectos.id_tren = trenes.id)
WHERE trayectos.nombre = 'Domainer';

SELECT * FROM trayectos
RIGHT JOIN trenes ON (trayectos.id_tren = trenes.id)
WHERE trayectos.nombre = 'Domainer';
SELECT * FROM public.pasajero 
RIGHT Join public.viaje 
ON (viaje.id_pasajero = pasajero.id);

SELECT * FROM public.pasajero 
RIGHT Join public.viaje 
ON (viaje.id_pasajero = pasajero.id) 
WHERE viaje.id IS NULL;

SELECT * FROM public.pasajero 
FULL OUTER Join public.viaje 
ON (viaje.id_pasajero = pasajero.id);

SELECT * FROM public.pasajero 
FULL OUTER Join public.viaje 
ON (viaje.id_pasajero = pasajero.id) 
WHERE viaje.id_pasajero IS NULL OR pasajero.id IS NULL;

SELECT *
FROM trenes
JOIN trayectos ON trayectos.id_tren = trenes.id
WHERE trenes.capacidad >= ‘100’;

Les compartó mis soluciones:

  • Estación
SELECT * FROM public.trayecto
RIGHT JOIN estacion ON (trayecto.id_estacion = estacion.id)
WHERE trayecto.id IS NULL
ORDER BY trayecto.id;
  • Tren
SELECT * FROM public.trayecto
RIGHT JOIN tren ON (trayecto.id_tren = tren.id)
WHERE trayecto.id IS NULL
ORDER BY tren.id;
--Pasajero/viaje
SELECT * FROM pasajero
JOIN viaje on (viaje.id_pasajero = pasajero.id);

--pasajero/viaje cuando is null
SELECT * FROM pasajero
LEFT JOIN viaje on (viaje.id_pasajero = pasajero.id)
WHERE viaje.id is NULL;

--estacion/tren
SELECT * FROM estacion
JOIN tren ON (tren.id =estacion.id);```

Para unir trenes y estaciones:

SELECT * FROM trayectos AS t
JOIN trenes AS tr ON (t.id_tren = tr.id )
JOIN estaciones AS s ON (t.id_estacion = s.id );
SELECT p.nombre AS PASAJERO, v.inicio AS INICIO_VIAJE, v.fin FIN_VIAJE, 
       e.nombre AS ESTACION, e.direccion AS DIRECCION_EST, tr.modelo AS MODELO_TREN
FROM pasajero AS p
INNER JOIN viaje AS v
ON p.id = v.id_pasajero
INNER JOIN trayecto AS t
ON v.id_trayecto = t.id
INNER JOIN estacion AS e
ON t.id_estacion = e.id
INNER JOIN tren AS tr
ON  t.id_tren = tr.id;