mysql - relacionadas - inner join

Consulta SQL devuelve datos de múltiples tablas

Question

Me gustaría saber lo siguiente:

• ¿Cómo obtener datos de varias tablas en mi base de datos?
• ¿Qué tipos de métodos hay para hacer esto?
• ¿Qué son las uniones y los sindicatos y en qué se diferencian entre sí?
• ¿Cuándo debo usar cada uno en comparación con los otros?

Planeo usar esto en mi aplicación (por ejemplo, PHP), pero no quiero ejecutar varias consultas en la base de datos, ¿qué opciones tengo para obtener datos de varias tablas en una sola consulta?

Nota: Estoy escribiendo esto ya que me gustaría poder enlazar con una guía bien escrita sobre las numerosas preguntas que encuentro constantemente en la cola de PHP, así que puedo vincularme con esto para obtener más detalles cuando publique una respuesta.

Las respuestas cubren lo siguiente:

1. Parte 1 - Uniones y uniones
2. Parte 2 - Subconsultas
3. Parte 3 - Trucos y código eficiente
4. Parte 4 - Subconsultas en la cláusula From
5. Parte 5 - Bolsa mixta de trucos de John

Answer 1

Parte 1 - Uniones y uniones

Esta respuesta cubre:

1. Parte 1
   • Unir dos o más tablas utilizando una combinación interna (consulte la entrada de wikipedia para obtener información adicional)
   • Cómo utilizar una consulta de unión
   • Uniones externas izquierda y derecha (esta respuesta de stackOverflow es excelente para describir los tipos de uniones)
   • Intersecta las consultas (y cómo reproducirlas si su base de datos no las admite): esta es una función de SQL-Server ( ver información ) y es parte de la razón por la que escribí todo esto en primer lugar.
2. Parte 2
   • Subconsultas: qué son, dónde se pueden usar y qué vigilar
   • Cartesiano se une a AKA - ¡Oh, la miseria!

Hay varias formas de recuperar datos de varias tablas en una base de datos. En esta respuesta, usaré la sintaxis de unión ANSI-92. Esto puede ser diferente a una serie de otros tutoriales que usan la sintaxis ANSI-89 anterior (y si está acostumbrado a 89, puede parecer mucho menos intuitivo, pero todo lo que puedo decir es probarlo), ya que es mucho más fácil para entender cuando las consultas comienzan a hacerse más complejas. ¿Por qué usarlo? ¿Hay una ganancia de rendimiento? La respuesta corta es no, pero es más fácil de leer una vez que te acostumbras. Es más fácil leer las consultas escritas por otras personas que utilizan esta sintaxis.

También voy a utilizar el concepto de un pequeño patio que tiene una base de datos para realizar un seguimiento de los coches que tiene disponibles. El propietario lo ha contratado como su informático de TI y espera que usted pueda dejarle los datos que solicita en un abrir y cerrar de ojos.

He hecho una serie de tablas de búsqueda que se utilizarán en la tabla final. Esto nos dará un modelo razonable para trabajar. Para comenzar, ejecutaré mis consultas en una base de datos de ejemplo que tiene la siguiente estructura. Trataré de pensar en los errores comunes que se cometen al comenzar y explicaré qué es lo que no funciona en ellos, así como, por supuesto, mostrar cómo corregirlos.

La primera tabla es simplemente una lista de colores para que sepamos qué colores tenemos en el patio de autos.

mysql> create table colors(id int(3) not null auto_increment primary key,
-> color varchar(15), paint varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from colors;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| color | varchar(15) | YES | | NULL | |
| paint | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> insert into colors (color, paint) values (''Red'', ''Metallic''),
-> (''Green'', ''Gloss''), (''Blue'', ''Metallic''),
-> (''White'' ''Gloss''), (''Black'' ''Gloss'');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from colors;
+----+-------+----------+
| id | color | paint |
+----+-------+----------+
| 1 | Red | Metallic |
| 2 | Green | Gloss |
| 3 | Blue | Metallic |
| 4 | White | Gloss |
| 5 | Black | Gloss |
+----+-------+----------+
5 rows in set (0.00 sec)

La tabla de marcas identifica las diferentes marcas de autos que posiblemente se podrían vender en el patio.

mysql> create table brands (id int(3) not null auto_increment primary key,
-> brand varchar(15));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from brands;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| brand | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> insert into brands (brand) values (''Ford''), (''Toyota''),
-> (''Nissan''), (''Smart''), (''BMW'');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from brands;
+----+--------+
| id | brand |
+----+--------+
| 1 | Ford |
| 2 | Toyota |
| 3 | Nissan |
| 4 | Smart |
| 5 | BMW |
+----+--------+
5 rows in set (0.00 sec)

La tabla de modelos cubrirá diferentes tipos de autos, para esto será más sencillo utilizar diferentes tipos de autos en lugar de modelos de autos reales.

mysql> create table models (id int(3) not null auto_increment primary key,
-> model varchar(15));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from models;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| model | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into models (model) values (''Sports''), (''Sedan''), (''4WD''), (''Luxury'');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from models;
+----+--------+
| id | model |
+----+--------+
| 1 | Sports |
| 2 | Sedan |
| 3 | 4WD |
| 4 | Luxury |
+----+--------+
4 rows in set (0.00 sec)

Y finalmente, para atar todas estas otras mesas, la mesa que une todo. El campo ID es en realidad el número de lote único utilizado para identificar los automóviles.

mysql> create table cars (id int(3) not null auto_increment primary key,
-> color int(3), brand int(3), model int(3));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from cars;
+-------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| color | int(3) | YES | | NULL | |
| brand | int(3) | YES | | NULL | |
| model | int(3) | YES | | NULL | |
+-------+--------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into cars (color, brand, model) values (1,2,1), (3,1,2), (5,3,1),
-> (4,4,2), (2,2,3), (3,5,4), (4,1,3), (2,2,1), (5,2,3), (4,5,1);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from cars;
+----+-------+-------+-------+
| id | color | brand | model |
+----+-------+-------+-------+
| 1 | 1 | 2 | 1 |
| 2 | 3 | 1 | 2 |
| 3 | 5 | 3 | 1 |
| 4 | 4 | 4 | 2 |
| 5 | 2 | 2 | 3 |
| 6 | 3 | 5 | 4 |
| 7 | 4 | 1 | 3 |
| 8 | 2 | 2 | 1 |
| 9 | 5 | 2 | 3 |
| 10 | 4 | 5 | 1 |
+----+-------+-------+-------+
10 rows in set (0.00 sec)

Esto nos dará suficientes datos (espero) para cubrir los ejemplos a continuación de diferentes tipos de combinaciones y también datos suficientes para que valgan la pena.

Así que, entrando en eso, el jefe quiere saber las identificaciones de todos los autos deportivos que tiene .

Esta es una simple combinación de dos mesas. Tenemos una tabla que identifica el modelo y la tabla con el stock disponible en ella. Como puede ver, los datos en la columna del model de la tabla de cars se relacionan con la columna de models de la tabla de cars que tenemos. Ahora, sabemos que la tabla de modelos tiene una ID de 1 para Sports así que escribamos la unión.

select
ID,
model
from
cars
join models
on model=ID

Así que esta consulta se ve bien ¿verdad? Hemos identificado las dos tablas y contenemos la información que necesitamos y usamos una unión que identifica correctamente en qué columnas se unen.

ERROR 1052 (23000): Column ''ID'' in field list is ambiguous

¡Oh no! Un error en nuestra primera consulta! Sí, y es una ciruela. Verá, la consulta efectivamente tiene las columnas correctas, pero algunas de ellas existen en ambas tablas, por lo que la base de datos se confunde sobre qué columna real queremos decir y dónde. Hay dos soluciones para resolver esto. La primera es agradable y simple, podemos usar tableName.columnName para decirle a la base de datos exactamente lo que queremos decir, así:

select
cars.ID,
models.model
from
cars
join models
on cars.model=models.ID
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
| 2 | Sedan |
| 4 | Sedan |
| 5 | 4WD |
| 7 | 4WD |
| 9 | 4WD |
| 6 | Luxury |
+----+--------+
10 rows in set (0.00 sec)

El otro es probablemente más usado y se llama alias de tablas. Las tablas en este ejemplo tienen nombres simples, bonitos y cortos, pero escribir algo como KPI_DAILY_SALES_BY_DEPARTMENT probablemente envejecerá rápidamente, por lo que una forma simple es apodar la tabla de esta manera:

select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID

Ahora, volvamos a la petición. Como puede ver, tenemos la información que necesitamos, pero también tenemos información que no se solicitó, por lo que debemos incluir una cláusula donde en la declaración para obtener solo los automóviles deportivos como se solicitó. Como prefiero el método de alias de la tabla en lugar de usar los nombres de la tabla una y otra vez, lo seguiré aplicando desde este punto en adelante.

Claramente, necesitamos agregar una cláusula where a nuestra consulta. Podemos identificar los autos deportivos ya sea por ID=1 o por el model=''Sports'' . A medida que la ID se indexa y la clave principal (y resulta que se escribe menos), utilicemos eso en nuestra consulta.

select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
where
b.ID=1
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)

¡Bingo! El jefe es feliz. Por supuesto, siendo un jefe y nunca estando feliz con lo que pidió, él mira la información y luego dice que también quiero los colores .

Bien, ya tenemos una buena parte de nuestra consulta escrita, pero necesitamos usar una tercera tabla que sea de colores. Ahora, nuestra tabla principal de información sobre cars almacena la identificación del color del auto y esto se vincula con la columna de identificación de los colores. Entonces, de manera similar a la original, podemos unirnos a una tercera tabla:

select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
where
b.ID=1
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)

Maldición, aunque la tabla se unió correctamente y las columnas relacionadas estaban vinculadas, se nos olvidó extraer la información real de la nueva tabla que acabamos de vincular.

select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
where
b.ID=1
+----+--------+-------+
| ID | model | color |
+----+--------+-------+
| 1 | Sports | Red |
| 8 | Sports | Green |
| 10 | Sports | White |
| 3 | Sports | Black |
+----+--------+-------+
4 rows in set (0.00 sec)

Correcto, ese es el jefe de nuestra espalda por un momento. Ahora, para explicar algo de esto con un poco más de detalle. Como puede ver, la cláusula from en nuestra declaración vincula nuestra tabla principal (a menudo utilizo una tabla que contiene información en lugar de una tabla de consulta o dimensión. La consulta funcionaría igual de bien con todas las tablas cambiadas, pero tiene menos sentido cuando volvamos a esta consulta para leerla en unos pocos meses, por lo que a menudo es mejor tratar de escribir una consulta que sea agradable y fácil de entender: distribúyala de forma intuitiva, use una buena sangría para que todo sea tan claro como puede ser. Si continúa enseñando a otros, intente inculcar estas características en sus consultas, especialmente si va a solucionarlos.

Es totalmente posible seguir vinculando más y más tablas de esta manera.

select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1

Aunque olvidé incluir una tabla en la que podríamos unir más de una columna en la declaración de join , aquí hay un ejemplo. Si la tabla de models tenía modelos específicos de la marca y, por lo tanto, también tenía una columna llamada brand que se vinculaba de nuevo a la tabla de brands en el campo ID , se podría hacer así:

select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
and b.brand=d.ID
where
b.ID=1

Como puede ver, la consulta anterior no solo vincula las tablas unidas a la tabla principal de cars , sino que también especifica uniones entre las tablas ya unidas. Si esto no se hizo, el resultado se llama una unión cartesiana, que es hablar mal para mal. Una unión cartesiana es una donde las filas se devuelven porque la información no le dice a la base de datos cómo limitar los resultados, por lo que la consulta devuelve todas las filas que se ajustan a los criterios.

Entonces, para dar un ejemplo de una unión cartesiana, ejecutemos la siguiente consulta:

select
a.ID,
b.model
from
cars a
join models b
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 1 | Sedan |
| 1 | 4WD |
| 1 | Luxury |
| 2 | Sports |
| 2 | Sedan |
| 2 | 4WD |
| 2 | Luxury |
| 3 | Sports |
| 3 | Sedan |
| 3 | 4WD |
| 3 | Luxury |
| 4 | Sports |
| 4 | Sedan |
| 4 | 4WD |
| 4 | Luxury |
| 5 | Sports |
| 5 | Sedan |
| 5 | 4WD |
| 5 | Luxury |
| 6 | Sports |
| 6 | Sedan |
| 6 | 4WD |
| 6 | Luxury |
| 7 | Sports |
| 7 | Sedan |
| 7 | 4WD |
| 7 | Luxury |
| 8 | Sports |
| 8 | Sedan |
| 8 | 4WD |
| 8 | Luxury |
| 9 | Sports |
| 9 | Sedan |
| 9 | 4WD |
| 9 | Luxury |
| 10 | Sports |
| 10 | Sedan |
| 10 | 4WD |
| 10 | Luxury |
+----+--------+
40 rows in set (0.00 sec)

Dios mío, eso es feo. Sin embargo, en lo que respecta a la base de datos, es exactamente lo que se solicitó. En la consulta, solicitamos la ID de los cars y el model de los models . Sin embargo, debido a que no especificamos cómo unir las tablas, la base de datos ha comparado cada fila de la primera tabla con cada fila de la segunda tabla.

Bien, entonces el jefe está de vuelta, y quiere más información de nuevo. Quiero la misma lista, pero también incluir 4WDs en ella .

Sin embargo, esto nos da una gran excusa para ver dos formas diferentes de lograr esto. Podríamos agregar otra condición a la cláusula where como esta:

select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
or b.ID=3

Si bien lo anterior funcionará perfectamente bien, veamos de manera diferente, esta es una gran excusa para mostrar cómo funcionará una consulta de union .

Sabemos que lo siguiente devolverá todos los autos deportivos:

select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1

Y lo siguiente devolvería todos los 4WDs:

select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=3

Entonces, al agregar una union all entre union all cláusulas, los resultados de la segunda consulta se agregarán a los resultados de la primera consulta.

select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
union all
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=3
+----+--------+-------+
| ID | model | color |
+----+--------+-------+
| 1 | Sports | Red |
| 8 | Sports | Green |
| 10 | Sports | White |
| 3 | Sports | Black |
| 5 | 4WD | Green |
| 7 | 4WD | White |
| 9 | 4WD | Black |
+----+--------+-------+
7 rows in set (0.00 sec)

Como puede ver, los resultados de la primera consulta se devuelven primero, seguidos de los resultados de la segunda consulta.

En este ejemplo, por supuesto, habría sido mucho más fácil usar la primera consulta, pero union consultas de union pueden ser excelentes para casos específicos. Son una excelente manera de obtener resultados específicos de tablas de tablas que no se pueden unir fácilmente, o, por lo demás, tablas no relacionadas. Hay algunas reglas a seguir sin embargo.

• Los tipos de columna de la primera consulta deben coincidir con los tipos de columna de cada otra consulta a continuación.
• Los nombres de las columnas de la primera consulta se utilizarán para identificar el conjunto completo de resultados.
• El número de columnas en cada consulta debe ser el mismo.

Ahora, quizás se esté preguntando cuál es la diferencia entre usar union y union all . Una consulta de union eliminará duplicados, mientras que una union all no. Esto significa que hay un pequeño impacto en el rendimiento cuando se usa union sobre union all pero los resultados pueden valer la pena, aunque no especularé sobre este tipo de cosas.

En esta nota, podría valer la pena señalar algunas notas adicionales aquí.

• Si quisiéramos ordenar los resultados, podemos usar un order by pero ya no puede usar el alias. En la consulta anterior, order by a.ID una order by a.ID resultaría en un error, en lo que respecta a los resultados, la columna se llama ID lugar de a.ID , incluso aunque se haya utilizado el mismo alias en ambas consultas.
• Solo podemos tener una order by declaración, y debe ser como la última declaración.

Para los siguientes ejemplos, estoy agregando algunas filas adicionales a nuestras tablas.

He añadido a Holden a la tabla de marcas. También he agregado una fila a los cars que tiene un valor de color de 12 , que no tiene ninguna referencia en la tabla de colores.

De acuerdo, el jefe ha vuelto, las peticiones de ladridos son: * ¡Quiero un recuento de cada marca que llevamos y la cantidad de autos que contiene! `- Típico, solo llegamos a una sección interesante de nuestra discusión y el jefe quiere más trabajo .

Rightyo, así que lo primero que debemos hacer es obtener una lista completa de posibles marcas.

select
a.brand
from
brands a
+--------+
| brand |
+--------+
| Ford |
| Toyota |
| Nissan |
| Smart |
| BMW |
| Holden |
+--------+
6 rows in set (0.00 sec)

Ahora, cuando unimos esto a nuestra mesa de autos obtenemos el siguiente resultado:

select
a.brand
from
brands a
join cars b
on a.ID=b.brand
group by
a.brand
+--------+
| brand |
+--------+
| BMW |
| Ford |
| Nissan |
| Smart |
| Toyota |
+--------+
5 rows in set (0.00 sec)

Lo que, por supuesto, es un problema: no vemos ninguna mención de la encantadora marca Holden que agregué.

Esto se debe a que una unión busca filas coincidentes en ambas tablas. Como no hay datos en los automóviles que sean del tipo Holden , no se devuelven. Aquí es donde podemos usar una unión outer . Esto devolverá todos los resultados de una tabla, ya sea que coincidan en la otra tabla o no:

select
a.brand
from
brands a
left outer join cars b
on a.ID=b.brand
group by
a.brand
+--------+
| brand |
+--------+
| BMW |
| Ford |
| Holden |
| Nissan |
| Smart |
| Toyota |
+--------+
6 rows in set (0.00 sec)

Ahora que tenemos eso, podemos agregar una función agregada encantadora para obtener un conteo y sacar al jefe de la espalda por un momento.

select
a.brand,
count(b.id) as countOfBrand
from
brands a
left outer join cars b
on a.ID=b.brand
group by
a.brand
+--------+--------------+
| brand | countOfBrand |
+--------+--------------+
| BMW | 2 |
| Ford | 2 |
| Holden | 0 |
| Nissan | 1 |
| Smart | 1 |
| Toyota | 5 |
+--------+--------------+
6 rows in set (0.00 sec)

Y con eso, el jefe se escabulle.

Ahora, para explicar esto con más detalle, las uniones externas pueden ser del tipo left o right . La izquierda o la derecha definen qué tabla está completamente incluida. Una left outer join incluirá todas las filas de la tabla de la izquierda, mientras que (lo adivinó) una right outer join trae todos los resultados de la tabla de la derecha a los resultados.

Algunas bases de datos permitirán una full outer join que devolverá los resultados (coincidentes o no) de ambas tablas, pero esto no se admite en todas las bases de datos.

Ahora, probablemente en este momento, me pregunto si puede o no fusionar los tipos de unión en una consulta, y la respuesta es sí, absolutamente puede.

select
b.brand,
c.color,
count(a.id) as countOfBrand
from
cars a
right outer join brands b
on b.ID=a.brand
join colors c
on a.color=c.ID
group by
a.brand,
c.color
+--------+-------+--------------+
| brand | color | countOfBrand |
+--------+-------+--------------+
| Ford | Blue | 1 |
| Ford | White | 1 |
| Toyota | Black | 1 |
| Toyota | Green | 2 |
| Toyota | Red | 1 |
| Nissan | Black | 1 |
| Smart | White | 1 |
| BMW | Blue | 1 |
| BMW | White | 1 |
+--------+-------+--------------+
9 rows in set (0.00 sec)

Entonces, ¿por qué no son los resultados que se esperaban? Esto se debe a que, aunque hemos seleccionado la combinación externa de autos a marcas, no se especificó en la combinación de colores, por lo que esa combinación en particular solo traerá resultados que coincidan en ambas tablas.

Aquí está la consulta que funcionaría para obtener los resultados que esperábamos:

select
a.brand,
c.color,
count(b.id) as countOfBrand
from
brands a
left outer join cars b
on a.ID=b.brand
left outer join colors c
on b.color=c.ID
group by
a.brand,
c.color
+--------+-------+--------------+
| brand | color | countOfBrand |
+--------+-------+--------------+
| BMW | Blue | 1 |
| BMW | White | 1 |
| Ford | Blue | 1 |
| Ford | White | 1 |
| Holden | NULL | 0 |
| Nissan | Black | 1 |
| Smart | White | 1 |
| Toyota | NULL | 1 |
| Toyota | Black | 1 |
| Toyota | Green | 2 |
| Toyota | Red | 1 |
+--------+-------+--------------+
11 rows in set (0.00 sec)

Como podemos ver, tenemos dos combinaciones externas en la consulta y los resultados se están obteniendo como se esperaba.

Ahora, ¿qué hay de esos otros tipos de uniones que preguntas? ¿Qué pasa con las intersecciones?

Bueno, no todas las bases de datos admiten la intersection pero casi todas las bases de datos te permitirán crear una intersección a través de una unión (o una declaración donde esté bien estructurada, como mínimo).

Una intersección es un tipo de unión algo similar a una union como se describe anteriormente, pero la diferencia es que solo devuelve filas de datos que son idénticas (y me refiero a idénticas) entre las diversas consultas individuales unidas por la unión. Solo se devolverán las filas que sean idénticas en todos los aspectos.

Un ejemplo simple sería como tal:

select
*
from
colors
where
ID>2
intersect
select
*
from
colors
where
id<4

Mientras que una consulta de union normal devolvería todas las filas de la tabla (la primera consulta que devuelve algo sobre ID>2 y la segunda con ID<4 ), lo que resultaría en un conjunto completo, una consulta de intersección solo devolvería la id=3 la fila id=3 ya que cumple ambos criterios.

Ahora, si su base de datos no admite una consulta de intersect , lo anterior puede realizarse fácilmente con la siguiente consulta:

select
a.ID,
a.color,
a.paint
from
colors a
join colors b
on a.ID=b.ID
where
a.ID>2
and b.ID<4
+----+-------+----------+
| ID | color | paint |
+----+-------+----------+
| 3 | Blue | Metallic |
+----+-------+----------+
1 row in set (0.00 sec)

Si desea realizar una intersección entre dos tablas diferentes utilizando una base de datos que no admite inherentemente una consulta de intersección, deberá crear una unión en cada columna de las tablas.

Answer 2

Parte 2 - Subconsultas

Bien, ahora el jefe ha vuelto a estallar. ¡Quiero una lista de todos nuestros autos con la marca y un total de la cantidad de esa marca que tenemos!

Esta es una gran oportunidad para usar el siguiente truco en nuestra bolsa de productos de SQL: la subconsulta. Si no está familiarizado con el término, una subconsulta es una consulta que se ejecuta dentro de otra consulta. Hay muchas maneras diferentes de usarlos.

Para nuestra solicitud, primero hagamos una consulta simple que listará cada automóvil y la marca:

select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID

Ahora, si quisiéramos simplemente obtener un conteo de autos clasificados por marca, por supuesto podríamos escribir esto:

select
b.brand,
count(a.ID) as countCars
from
cars a
join brands b
on a.brand=b.ID
group by
b.brand
+--------+-----------+
| brand | countCars |
+--------+-----------+
| BMW | 2 |
| Ford | 2 |
| Nissan | 1 |
| Smart | 1 |
| Toyota | 5 |
+--------+-----------+

Entonces, deberíamos poder simplemente agregar la función de conteo a nuestra consulta original, ¿no?

select
a.ID,
b.brand,
count(a.ID) as countCars
from
cars a
join brands b
on a.brand=b.ID
group by
a.ID,
b.brand
+----+--------+-----------+
| ID | brand | countCars |
+----+--------+-----------+
| 1 | Toyota | 1 |
| 2 | Ford | 1 |
| 3 | Nissan | 1 |
| 4 | Smart | 1 |
| 5 | Toyota | 1 |
| 6 | BMW | 1 |
| 7 | Ford | 1 |
| 8 | Toyota | 1 |
| 9 | Toyota | 1 |
| 10 | BMW | 1 |
| 11 | Toyota | 1 |
+----+--------+-----------+
11 rows in set (0.00 sec)

Lamentablemente, no, no podemos hacer eso. La razón es que cuando agregamos la ID del auto (columna a.ID) tenemos que agregarla al grupo antes de que, cuando la función de conteo funciona, solo haya una ID coincidente por ID.

Sin embargo, aquí es donde podemos usar una subconsulta; de hecho, podemos hacer dos tipos de subconsultas completamente diferentes que devolverán los mismos resultados que necesitamos para esto. La primera es simplemente poner la subconsulta en la cláusula de select . Esto significa que cada vez que obtenemos una fila de datos, la subconsulta se ejecutará, obtendrá una columna de datos y luego la insertará en nuestra fila de datos.

select
a.ID,
b.brand,
(
select
count(c.ID)
from
cars c
where
a.brand=c.brand
) as countCars
from
cars a
join brands b
on a.brand=b.ID
+----+--------+-----------+
| ID | brand | countCars |
+----+--------+-----------+
| 2 | Ford | 2 |
| 7 | Ford | 2 |
| 1 | Toyota | 5 |
| 5 | Toyota | 5 |
| 8 | Toyota | 5 |
| 9 | Toyota | 5 |
| 11 | Toyota | 5 |
| 3 | Nissan | 1 |
| 4 | Smart | 1 |
| 6 | BMW | 2 |
| 10 | BMW | 2 |
+----+--------+-----------+
11 rows in set (0.00 sec)

Y Bam !, esto nos haría a nosotros. Sin embargo, si se dio cuenta, esta subconsulta tendrá que ejecutarse para cada fila de datos que devolvamos. Incluso en este pequeño ejemplo, solo tenemos cinco marcas diferentes de automóviles, pero la subconsulta se ejecutó once veces, ya que tenemos once filas de datos que estamos devolviendo. Entonces, en este caso, no parece ser la forma más eficiente de escribir código.

Para un enfoque diferente, vamos a ejecutar una subconsulta y pretender que es una tabla:

select
a.ID,
b.brand,
d.countCars
from
cars a
join brands b
on a.brand=b.ID
join
(
select
c.brand,
count(c.ID) as countCars
from
cars c
group by
c.brand
) d
on a.brand=d.brand
+----+--------+-----------+
| ID | brand | countCars |
+----+--------+-----------+
| 1 | Toyota | 5 |
| 2 | Ford | 2 |
| 3 | Nissan | 1 |
| 4 | Smart | 1 |
| 5 | Toyota | 5 |
| 6 | BMW | 2 |
| 7 | Ford | 2 |
| 8 | Toyota | 5 |
| 9 | Toyota | 5 |
| 10 | BMW | 2 |
| 11 | Toyota | 5 |
+----+--------+-----------+
11 rows in set (0.00 sec)

Bien, tenemos los mismos resultados (ordenados ligeramente diferentes, parece que la base de datos quería devolver los resultados ordenados por la primera columna que seleccionamos esta vez), pero los mismos números correctos.

Entonces, ¿cuál es la diferencia entre los dos y cuándo debemos usar cada tipo de subconsulta? Primero, asegurémonos de que entendemos cómo funciona esa segunda consulta. Seleccionamos dos tablas en la cláusula from de nuestra consulta, y luego escribimos una consulta y le dijimos a la base de datos que en realidad era una tabla, con lo cual la base de datos está perfectamente satisfecha. Puede haber algunos beneficios al usar este método (así como algunas limitaciones). Lo más importante es que esta subconsulta corrió una vez . Si nuestra base de datos contenía un gran volumen de datos, podría haber una mejora masiva con respecto al primer método. Sin embargo, como estamos usando esto como una tabla, tenemos que traer filas adicionales de datos, para que realmente puedan unirse a nuestras filas de datos. También debemos asegurarnos de que haya suficientes filas de datos si vamos a utilizar una combinación simple como en la consulta anterior. Si recuerdas, la unión solo hará retroceder las filas que tengan datos coincidentes en ambos lados de la unión. Si no tenemos cuidado, esto podría resultar en que no se devuelvan datos válidos de nuestra tabla de autos si no hubiera una fila coincidente en esta subconsulta.

Ahora, mirando hacia atrás en la primera subconsulta, también hay algunas limitaciones. Debido a que estamos recuperando los datos en una sola fila, SOLAMENTE podemos recuperar una fila de datos. Las subconsultas usadas en la cláusula de select de una consulta muy a menudo usan solo una función agregada como sum , count , max u otra función agregada similar. No tienen que hacerlo, pero a menudo es así como están escritos.

Entonces, antes de continuar, veamos rápidamente dónde más podemos usar una subconsulta. Podemos usarlo en la cláusula where ; ahora, este ejemplo está un poco diseñado como en nuestra base de datos, hay mejores maneras de obtener los siguientes datos, pero como es solo un ejemplo, veamos:

select
ID,
brand
from
brands
where
brand like ''%o%''
+----+--------+
| ID | brand |
+----+--------+
| 1 | Ford |
| 2 | Toyota |
| 6 | Holden |
+----+--------+
3 rows in set (0.00 sec)

Esto nos devuelve una lista de ID de marca y nombres de marca (la segunda columna solo se agrega para mostrarnos las marcas) que contienen la letra o en el nombre.

Ahora, podríamos usar los resultados de esta consulta en una cláusula donde esto:

select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID
where
a.brand in
(
select
ID
from
brands
where
brand like ''%o%''
)
+----+--------+
| ID | brand |
+----+--------+
| 2 | Ford |
| 7 | Ford |
| 1 | Toyota |
| 5 | Toyota |
| 8 | Toyota |
| 9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)

Como puede ver, aunque la subconsulta estaba devolviendo las tres identificaciones de marca, nuestra tabla de autos solo tenía entradas para dos de ellas.

En este caso, para más detalles, la subconsulta está funcionando como si escribiéramos el siguiente código:

select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID
where
a.brand in (1,2,6)
+----+--------+
| ID | brand |
+----+--------+
| 1 | Toyota |
| 2 | Ford |
| 5 | Toyota |
| 7 | Ford |
| 8 | Toyota |
| 9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)

Nuevamente, puede ver cómo una subconsulta frente a entradas manuales ha cambiado el orden de las filas al regresar de la base de datos.

Mientras discutimos las subconsultas, veamos qué más podemos hacer con una subconsulta:

• Puede colocar una subconsulta dentro de otra subconsulta, y así sucesivamente. Hay un límite que depende de su base de datos, pero a falta de funciones recursivas de algún programador loco y maníaco, la mayoría de la gente nunca alcanzará ese límite.
• Puede colocar una serie de subconsultas en una sola consulta, unas pocas en la selectcláusula, algunas en la fromcláusula y un par más en la wherecláusula. Solo recuerde que cada una de las que ingresa hace que su consulta sea más compleja y es probable que demore más. ejecutar.

Si necesita escribir algún código eficiente, puede ser beneficioso escribir la consulta de varias maneras y ver (ya sea programándola o usando un plan de explicación) cuál es la consulta óptima para obtener sus resultados. La primera forma en que funciona puede no ser siempre la mejor manera de hacerlo.

Answer 3

Parte 3 - Trucos y código eficiente

MySQL en () eficiencia

Pensé que agregaría algunos bits adicionales, para sugerencias y trucos que han surgido.

Una pregunta que ver hecho un poco justo, es ¿Cómo llego filas no coincidentes de dos tablas y veo la respuesta más comúnmente aceptada como algo parecido a lo siguiente (basado en nuestros coches y marcas de mesa - el cual ha Holden aparece como una marca, pero no aparece en la tabla de autos):

select
a.ID,
a.brand
from
brands a
where
a.ID not in(select brand from cars)

Y sí funcionará.

+----+--------+
| ID | brand |
+----+--------+
| 6 | Holden |
+----+--------+
1 row in set (0.00 sec)

Sin embargo, es no eficiente en alguna base de datos. Aquí hay un enlace a una pregunta de desbordamiento de pila sobre este tema, y ​​aquí hay un excelente artículo en profundidad si quieres entrar en el meollo de la cuestión.

La respuesta corta es que si el optimizador no lo maneja de manera eficiente, puede ser mucho mejor usar una consulta como la siguiente para obtener filas no coincidentes:

select
a.brand
from
brands a
left join cars b
on a.id=b.brand
where
b.brand is null
+--------+
| brand |
+--------+
| Holden |
+--------+
1 row in set (0.00 sec)

Actualizar tabla con la misma tabla en subconsulta

Ahhh, otro antiguo pero bueno: el antiguo No se pueden especificar las ''marcas'' de la tabla de destino para actualizar en la cláusula FROM .

MySQL no le permitirá ejecutar una update...consulta con una subselección en la misma tabla. Ahora, podrías estar pensando, ¿por qué no simplemente pegarlo en la cláusula donde está bien? Pero, ¿qué sucede si desea actualizar solo la fila con la max()fecha entre un montón de otras filas? No puedes hacer eso exactamente en una cláusula donde.

update
brands
set
brand=''Holden''
where
id=
(select
id
from
brands
where
id=6);
ERROR 1093 (HY000): You can''t specify target table ''brands''
for update in FROM clause

Entonces, no podemos hacer eso, ¿eh? Bueno no exactamente. Existe una solución disimulada que un número sorprendentemente grande de usuarios no conoce, aunque sí incluye algunos hackers a los que deberás prestar atención.

Puede pegar la subconsulta dentro de otra subconsulta, lo que deja suficiente espacio entre las dos consultas para que funcione. Sin embargo, tenga en cuenta que podría ser más seguro mantener la consulta dentro de una transacción, esto evitará que se realicen otros cambios en las tablas mientras se ejecuta la consulta.

update
brands
set
brand=''Holden''
where id=
(select
id
from
(select
id
from
brands
where
id=6
)
as updateTable);
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1 Changed: 0 Warnings: 0

Answer 4

Ok, encontré esta publicación muy interesante y me gustaría compartir algunos de mis conocimientos sobre cómo crear una consulta. Gracias por este Fluffeh . Otros que pueden leer esto y sentir que estoy equivocado son 101% gratis para editar y criticar mi respuesta. ( Honestamente, me siento muy agradecido por corregir mis errores).

Estaré publicando algunas de las preguntas frecuentes en la etiqueta MySQL .

Truco No. 1 ( filas que coinciden con múltiples condiciones )

Dado este esquema

CREATE TABLE MovieList
(
ID INT,
MovieName VARCHAR(25),
CONSTRAINT ml_pk PRIMARY KEY (ID),
CONSTRAINT ml_uq UNIQUE (MovieName)
);
INSERT INTO MovieList VALUES (1, ''American Pie'');
INSERT INTO MovieList VALUES (2, ''The Notebook'');
INSERT INTO MovieList VALUES (3, ''Discovery Channel: Africa'');
INSERT INTO MovieList VALUES (4, ''Mr. Bean'');
INSERT INTO MovieList VALUES (5, ''Expendables 2'');
CREATE TABLE CategoryList
(
MovieID INT,
CategoryName VARCHAR(25),
CONSTRAINT cl_uq UNIQUE(MovieID, CategoryName),
CONSTRAINT cl_fk FOREIGN KEY (MovieID) REFERENCES MovieList(ID)
);
INSERT INTO CategoryList VALUES (1, ''Comedy'');
INSERT INTO CategoryList VALUES (1, ''Romance'');
INSERT INTO CategoryList VALUES (2, ''Romance'');
INSERT INTO CategoryList VALUES (2, ''Drama'');
INSERT INTO CategoryList VALUES (3, ''Documentary'');
INSERT INTO CategoryList VALUES (4, ''Comedy'');
INSERT INTO CategoryList VALUES (5, ''Comedy'');
INSERT INTO CategoryList VALUES (5, ''Action'');

PREGUNTA

Encuentra todas las películas que pertenecen al menos a Romance categorías Comedy y Romance .

Solución

Esta pregunta puede ser muy difícil a veces. Puede parecer que una consulta como esta será la respuesta:

SELECT DISTINCT a.MovieName
FROM MovieList a
INNER JOIN CategoryList b
ON a.ID = b.MovieID
WHERE b.CategoryName = ''Comedy'' AND
b.CategoryName = ''Romance''

Demostración de SQLFiddle

lo que definitivamente está muy mal porque no produce ningún resultado . La explicación de esto es que solo hay un valor válido de CategoryName en cada fila . Por ejemplo, la primera condición devuelve verdadero , la segunda condición siempre es falsa. Por lo tanto, al usar el operador AND , ambas condiciones deben ser verdaderas; De lo contrario, será falso. Otra consulta es así,

SELECT DISTINCT a.MovieName
FROM MovieList a
INNER JOIN CategoryList b
ON a.ID = b.MovieID
WHERE b.CategoryName IN (''Comedy'',''Romance'')

Demostración de SQLFiddle

y el resultado sigue siendo incorrecto porque coincide con el registro que tiene al menos una coincidencia en la categoryName . La solución real sería contar el número de instancias de grabación por película . El número de instancias debe coincidir con el número total de los valores suministrados en la condición.

SELECT a.MovieName
FROM MovieList a
INNER JOIN CategoryList b
ON a.ID = b.MovieID
WHERE b.CategoryName IN (''Comedy'',''Romance'')
GROUP BY a.MovieName
HAVING COUNT(*) = 2

Demostración de SQLFiddle (la respuesta)

• SQL de la división relacional

Truco No. 2 ( registro máximo para cada entrada )

Dado el esquema,

CREATE TABLE Software
(
ID INT,
SoftwareName VARCHAR(25),
Descriptions VARCHAR(150),
CONSTRAINT sw_pk PRIMARY KEY (ID),
CONSTRAINT sw_uq UNIQUE (SoftwareName)
);
INSERT INTO Software VALUES (1,''PaintMe'',''used for photo editing'');
INSERT INTO Software VALUES (2,''World Map'',''contains map of different places of the world'');
INSERT INTO Software VALUES (3,''Dictionary'',''contains description, synonym, antonym of the words'');
CREATE TABLE VersionList
(
SoftwareID INT,
VersionNo INT,
DateReleased DATE,
CONSTRAINT sw_uq UNIQUE (SoftwareID, VersionNo),
CONSTRAINT sw_fk FOREIGN KEY (SOftwareID) REFERENCES Software(ID)
);
INSERT INTO VersionList VALUES (3, 2, ''2009-12-01'');
INSERT INTO VersionList VALUES (3, 1, ''2009-11-01'');
INSERT INTO VersionList VALUES (3, 3, ''2010-01-01'');
INSERT INTO VersionList VALUES (2, 2, ''2010-12-01'');
INSERT INTO VersionList VALUES (2, 1, ''2009-12-01'');
INSERT INTO VersionList VALUES (1, 3, ''2011-12-01'');
INSERT INTO VersionList VALUES (1, 2, ''2010-12-01'');
INSERT INTO VersionList VALUES (1, 1, ''2009-12-01'');
INSERT INTO VersionList VALUES (1, 4, ''2012-12-01'');

PREGUNTA

Encuentra la última versión en cada software. Muestre las siguientes columnas: LatestVersion SoftwareName , Descriptions , LatestVersion ( de la columna VersionNo ), DateReleased

Solución

Algunos desarrolladores de SQL utilizan erróneamente la función agregada MAX() . Tienden a crear así,

SELECT a.SoftwareName, a.Descriptions,
MAX(b.VersionNo) AS LatestVersion, b.DateReleased
FROM Software a
INNER JOIN VersionList b
ON a.ID = b.SoftwareID
GROUP BY a.ID
ORDER BY a.ID

Demostración de SQLFiddle

(la mayoría de los RDBMS generan un error de sintaxis al no especificar algunas de las columnas no agregadas en el group by cláusula ) el resultado produce la LatestVersion correcta en cada software pero, obviamente, las DateReleased son incorrectas. MySQL no es compatible con las Window Functions y Common Table Expression como algunos RDBMS ya lo hacen. La solución a este problema es crear una subquery que obtenga la versionNo máxima individual No en cada software y luego se unirá en las otras tablas.

SELECT a.SoftwareName, a.Descriptions,
b.LatestVersion, c.DateReleased
FROM Software a
INNER JOIN
(
SELECT SoftwareID, MAX(VersionNO) LatestVersion
FROM VersionList
GROUP BY SoftwareID
) b ON a.ID = b.SoftwareID
INNER JOIN VersionList c
ON c.SoftwareID = b.SoftwareID AND
c.VersionNO = b.LatestVersion
GROUP BY a.ID
ORDER BY a.ID

Demostración de SQLFiddle (la respuesta)

Así que eso fue todo. Pronto publicaré otra, ya que recuerdo cualquier otra pregunta frecuente en la etiqueta MySQL . Gracias por leer este pequeño artículo. Espero que al menos tengas un poco de conocimiento de esto.

ACTUALIZACIÓN 1

Truco No. 3 ( Encontrar el último registro entre dos ID )

Esquema dado

CREATE TABLE userList
(
ID INT,
NAME VARCHAR(20),
CONSTRAINT us_pk PRIMARY KEY (ID),
CONSTRAINT us_uq UNIQUE (NAME)
);
INSERT INTO userList VALUES (1, ''Fluffeh'');
INSERT INTO userList VALUES (2, ''John Woo'');
INSERT INTO userList VALUES (3, ''hims056'');
CREATE TABLE CONVERSATION
(
ID INT,
FROM_ID INT,
TO_ID INT,
MESSAGE VARCHAR(250),
DeliveryDate DATE
);
INSERT INTO CONVERSATION VALUES (1, 1, 2, ''hi john'', ''2012-01-01'');
INSERT INTO CONVERSATION VALUES (2, 2, 1, ''hello fluff'', ''2012-01-02'');
INSERT INTO CONVERSATION VALUES (3, 1, 3, ''hey hims'', ''2012-01-03'');
INSERT INTO CONVERSATION VALUES (4, 1, 3, ''please reply'', ''2012-01-04'');
INSERT INTO CONVERSATION VALUES (5, 3, 1, ''how are you?'', ''2012-01-05'');
INSERT INTO CONVERSATION VALUES (6, 3, 2, ''sample message!'', ''2012-01-05'');

PREGUNTA

Encuentra la última conversación entre dos usuarios.

Solución

SELECT b.Name SenderName,
c.Name RecipientName,
a.Message,
a.DeliveryDate
FROM Conversation a
INNER JOIN userList b
ON a.From_ID = b.ID
INNER JOIN userList c
ON a.To_ID = c.ID
WHERE (LEAST(a.FROM_ID, a.TO_ID), GREATEST(a.FROM_ID, a.TO_ID), DeliveryDate)
IN
(
SELECT LEAST(FROM_ID, TO_ID) minFROM,
GREATEST(FROM_ID, TO_ID) maxTo,
MAX(DeliveryDate) maxDate
FROM Conversation
GROUP BY minFROM, maxTo
)

Demostración de SQLFiddle

Answer 5

Espera que esto haga que encuentre las tablas a medida que las lees:

jsfiddle

mysql> show columns from colors;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| color | varchar(15) | YES | | NULL | |
| paint | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+

Answer 6

Puede utilizar el concepto de consultas múltiples en la palabra clave FROM. Déjame mostrarte un ejemplo:

SELECT DISTINCT e.id,e.name,d.name,lap.lappy LAPTOP_MAKE,c_loc.cnty COUNTY
FROM (
SELECT c.id cnty,l.name
FROM county c, location l
WHERE c.id=l.county_id AND l.end_Date IS NOT NULL
) c_loc, emp e
INNER JOIN dept d ON e.deptno =d.id
LEFT JOIN
(
SELECT l.id lappy, c.name cmpy
FROM laptop l, company c
WHERE l.make = c.name
) lap ON e.cmpy_id=lap.cmpy

Puedes usar tantas tablas como quieras. Use uniones externas y una unión donde sea necesario, incluso dentro de las subconsultas de tablas.

Es un método muy fácil para incluir tantas tablas como campos.