Datos de retorno de consulta SQL de varias tablas

Me gustaría saber lo siguiente:

  • cómo obtener datos de múltiples tablas en mi base de datos?
  • ¿Qué tipos de métodos hay para hacer esto?
  • ¿Qué son las uniones y los sindicatos y cómo son diferentes unos de otros?
  • ¿Cuándo debería usar cada uno en comparación con los demás?

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

Nota: Estoy escribiendo esto porque me gustaría poder vincular a una guía bien escrita sobre las numerosas preguntas que constantemente me encuentro en la cola de PHP, así que puedo acceder a este para obtener más detalles cuando publico una respuesta.

Las respuestas cubren lo siguiente:

  1. Parte 1 – Uniones y sindicatos
  2. Parte 2 – Subconsultas
  3. Parte 3 – Trucos y código eficiente
  4. Parte 4 – Subconsultas en la Cláusula De
  5. Parte 5 – Bolsa mezclada de trucos de John

Parte 1 – Uniones y sindicatos

Esta respuesta cubre:

  1. Parte 1
    • Unir dos o más tablas usando una unión interna (ver la entrada de wikipedia para obtener información adicional)
    • Cómo usar una consulta de unión
    • Uniones exteriores izquierda y derecha (esta respuesta stackOverflow es excelente para describir los tipos de uniones)
    • Intersecte 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 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 de qué preocuparse
    • Se une a Cartesian AKA – ¡Oh, la miseria!

Hay varias maneras de recuperar datos de múltiples tablas en una base de datos. En esta respuesta, usaré la syntax de unión ANSI-92. Esto puede ser diferente a otros tutoriales que usan la syntax anterior de ANSI-89 (y si está acostumbrado a 89, puede parecer mucho menos intuitivo, pero todo lo que puedo decir es que lo intente), ya que es mucho más fácil para entender cuándo las consultas comienzan a ser 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 consultas escritas por otras personas que usan esta syntax.

También voy a utilizar el concepto de un pequeño caryard que tiene una base de datos para realizar un seguimiento de los autos que tiene disponibles. El propietario lo ha contratado como su informático informático y espera que pueda enviarle los datos que solicite de un golpe.

He creado varias tablas de búsqueda que serán utilizadas por la mesa final. Esto nos dará un modelo razonable para trabajar. Para comenzar, ejecutaré mis consultas en una base de datos de ejemplo que tenga la siguiente estructura. Trataré de pensar en los errores comunes que se cometen al comenzar y explicarles qué es lo que falla, y también, cómo no, cómo corregirlos.

La primera tabla es simplemente una lista de colores para que sepamos qué colores tenemos en el patio del automóvil.

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 los carros que el carry podría vender.

 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 mesa modelo cubrirá diferentes tipos de autos, va a ser más simple para esto usar 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 tablas, la mesa que une todo. El campo ID es en realidad el número de lote único que se usa para identificar 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 los diferentes tipos de combinaciones y también dar los datos suficientes para que valgan la pena.

Así que al entrar en la arena, el jefe quiere saber las identificaciones de todos los autos deportivos que tiene .

Esta es una simple combinación de dos tablas. Tenemos una tabla que identifica el modelo y la tabla con el stock disponible en él. Como puede ver, los datos en la columna de 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 permite escribir la unión.

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

Entonces esta consulta se ve bien ¿verdad? Hemos identificado las dos tablas y contiene la información que necesitamos y usamos una combinación que identifica correctamente a qué columnas unir.

 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. El primero es bueno 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 utilizado y se denomina aliasing de tabla. Las tablas de este ejemplo tienen nombres sencillos y bonitos, 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, de vuelta a la solicitud. 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 WHERE en la statement para obtener solo los autos deportivos tal como se solicitó. Como prefiero el método de alias de tabla en lugar de usar los nombres de tabla una y otra vez, me apegaré a esto a partir de este punto en adelante.

Claramente, necesitamos agregar una cláusula where a nuestra consulta. Podemos identificar autos deportivos por ID=1 o model='Sports' . Como el ID está indexado y la clave principal (y resulta menos tipada), permitámoslo 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 está feliz. Por supuesto, al ser un jefe y no estar feliz con lo que pidió, mira la información y luego dice que también quiero los colores .

De acuerdo, entonces ya tenemos una buena parte de nuestra consulta escrita, pero necesitamos usar una tercera tabla que sea de colores. Ahora, nuestros cars principales de la mesa de información almacenan la identificación del color del auto y esto enlaza con la columna de ID de colores. Entonces, de manera similar al original, podemos unirnos a una tercera mesa:

 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 mesa estaba unida correctamente y las columnas relacionadas estaban vinculadas, olvidamos 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 de nuestra statement vincula nuestra tabla principal (a menudo utilizo una tabla que contiene información en lugar de una tabla de búsqueda o de dimensión. La consulta funcionaría igual de bien con las tablas todas conmutadas, pero tiene menos sentido cuando volvamos a esta consulta para leerla en unos meses, por lo que a menudo es mejor tratar de escribir una consulta que sea agradable y fácil de entender: plantearla de manera intuitiva, usar un buen sangrado para que todo quede 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 completamente 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 

Si bien olvidé incluir una tabla en la que es posible que desee unir más de una columna en la statement join , aquí hay un ejemplo. Si la tabla de models tenía modelos específicos de marca y, por lo tanto, también tenía una columna llamada brand que vinculaba de nuevo a la tabla de brands en el campo ID , se podí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 con la tabla principal de los cars , sino que también especifica las uniones entre las tablas ya unidas. Si esto no se hizo, el resultado se denomina unión cartesiana, que es dba hablar mal. Una unión cartesiana es aquella en la que se devuelven las filas porque la información no indica 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, como no especificamos cómo unir las tablas, la base de datos ha coincidido con cada fila de la primera tabla con cada fila de la segunda tabla.

De acuerdo, el jefe regresó y quiere más información de nuevo. Quiero la misma lista, pero también incluyo 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 union .

Sabemos que los siguientes devolverán 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 todas las 4WD:

 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 cláusula union all entre ellos, 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, hubiera sido mucho más fácil simplemente usar la primera consulta, pero union consultas union pueden ser excelentes para casos específicos. Son una excelente forma de devolver resultados específicos de tablas de tablas que no se unen fácilmente juntas, o para el caso, tablas completamente no relacionadas. Sin embargo, hay algunas reglas a seguir.

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

Ahora, usted se estará preguntando cuál es la diferencia entre usar el union y el union all . Una consulta union eliminará los duplicados, mientras que una union all no lo hará union all . Esto significa que hay un pequeño golpe de rendimiento cuando se usa union Over union all pero los resultados pueden valer la pena. Sin embargo, no voy a especular sobre ese tipo de cosas.

En esta nota, podría valer la pena anotar algunas notas adicionales aquí.

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

Para los próximos ejemplos, agrego algunas filas adicionales a nuestras tablas.

He agregado Holden a la tabla de marcas. También agregué una fila a cars que tienen el valor de color 12 , que no tiene referencia en la tabla de colores.

Está bien, el jefe está de vuelta, ladrando peticiones: * ¡Quiero un recuento de cada marca que llevamos y la cantidad de autos en ella! `- Típicamente, llegamos a una sección interesante de nuestra discusión y el jefe quiere más trabajo .

Rightyo, entonces 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 cual es, por supuesto, 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 devuelve. Aquí es donde podemos usar una combinación outer . Esto devolverá todos los resultados de una tabla, ya sea que estén o no coincidentes en la otra tabla:

 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 encantadora función agregada para obtener un conteo y sacar al jefe de nuestras espaldas 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, lejos el jefe se esconde.

Ahora, para explicar esto con más detalle, las uniones externas pueden ser del tipo left o right . La izquierda o la derecha define 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 traerá resultados (coincidentes o no) de ambas tablas, pero esto no es compatible con todas las bases de datos.

Ahora, probablemente me dé cuenta de que en este punto del tiempo, te preguntas si puedes o no fusionar tipos de combinación en una consulta, y la respuesta es sí, absolutamente puedes.

 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 automóviles 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 uniones externas en la consulta y los resultados vienen como se esperaba.

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

Bueno, no todas las bases de datos admiten la intersection pero prácticamente todas las bases de datos le permitirán crear una intersección a través de una combinación (o una statement where bien estructurada como mínimo).

Una Intersección es un tipo de unión algo similar a una union como se describió anteriormente, pero la diferencia es que solo devuelve filas de datos que son idénticos (y quiero decir idénticos) 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 devolvía algo con ID>2 y la segunda con ID<4 ) que daría como resultado un conjunto completo, una consulta intersectada solo devolvería la fila que coincide con el id=3 ya que cumple ambos criterios.

Ahora, si su base de datos no es compatible con una consulta intersect , lo anterior se puede realizar 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 en dos tablas diferentes utilizando una base de datos que no sea inherentemente compatible con una consulta de intersección, deberá crear una unión en cada columna de las tablas.

Bien, 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 pueden sentir que estoy equivocado son 101% libres de editar y criticar mi respuesta. ( Honestamente, me siento muy agradecido por corregir mi error (s)).

Publicaré algunas de las preguntas más frecuentes en la etiqueta de MySQL .


Truco n. ° 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, 'Dtwig'); 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

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 es siempre falsa. Por lo tanto, al usar el operador AND , ambas condiciones deberían 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 el categoryName . La solución real sería contar el número de instancias de registro por película . El número de instancia 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

Trick 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

Encuentre la última versión en cada software. Muestre las siguientes columnas: SoftwareName , Descriptions , LatestVersion ( from VersionNo column ), DateReleased

Solución

Algunos desarrolladores de SQL usan erróneamente la función de agregado 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 RDBMS genera un error de syntax debido a que no especifica algunas de las columnas no agregadas en la cláusula group by ) 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 ya que algunos RDBMS ya lo hacen. La solución alternativa a este problema consiste en crear una subquery que obtenga la versionNo máxima individual de cada software y, posteriormente, unirse a 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)


Entonces eso fue todo. Publicaré otro pronto, ya que recuerdo cualquier otra pregunta frecuente sobre la etiqueta de MySQL . Gracias por leer este pequeño artículo. Espero que al menos obtengas incluso un poco de conocimiento de esto.

ACTUALIZACIÓN 1


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

Dado el esquema

 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

Parte 2 – Subconsultas

Bien, ahora el jefe ha irrumpido de nuevo. Quiero una lista de todos nuestros automóviles con la marca y un total de cuántos de esa marca tenemos.

Esta es una gran oportunidad para usar el siguiente truco en nuestra bolsa de golosos 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 formas diferentes de usarlos.

Para nuestra solicitud, primero hagamos una consulta simple que enumerará cada auto y la marca:

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

Ahora, si quisiéramos obtener simplemente un recuento de autos ordenados por marca, podríamos, por supuesto, 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 simplemente agregar la función de recuento a nuestra consulta original, ¿verdad?

 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) 

Tristemente, no, no podemos hacer eso. La razón es que cuando agregamos el ID del carro (columna a.ID) tenemos que agregarlo al grupo por – por lo que ahora, cuando la función de recuento funciona, solo hay una identificación que coincida con cada ID.

Sin embargo, aquí es donde podemos usar una subconsulta; de hecho, podemos hacer dos tipos de subconsulta completamente diferentes que devolverán los mismos resultados que necesitamos para esto. El primero es simplemente poner la subconsulta en la cláusula de select . Esto significa que cada vez que obtengamos 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. Sin embargo, si lo has notado, esta sub consulta tendrá que ejecutarse para cada fila de datos que devolvemos. Incluso en este pequeño ejemplo, solo tenemos cinco marcas diferentes de automóviles, pero la subconsulta se realizó once veces, ya que tenemos once filas de datos que estamos devolviendo. Entonces, en este caso, no parece la forma más eficiente de escribir código.

Para un enfoque diferente, ejecutemos una subconsulta y pretendamos 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) 

De acuerdo, tenemos los mismos resultados (ordenados un poco diferentes, parece que la base de datos quería devolver los resultados ordenados por la primera columna que elegimos esta vez), pero con los mismos números correctos.

Entonces, ¿cuál es la diferencia entre los dos, y cuándo deberíamos usar cada tipo de subconsulta? Primero, asegurémonos de entender cómo funciona la 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 de hecho era una tabla, con lo que la base de datos está completamente satisfecha. Puede haber algunos beneficios al usar este método (así como algunas limitaciones). Lo más importante es que esta subconsulta se ejecutó una vez . Si nuestra base de datos contiene un gran volumen de datos, podría haber una mejora masiva sobre el primer método. Sin embargo, como estamos usando esto como una tabla, tenemos que traer filas de datos adicionales, de modo que puedan unirse de nuevo a nuestras filas de datos. También debemos asegurarnos de que haya suficientes filas de datos si vamos a usar una combinación simple como en la consulta anterior. Si lo recuerda, la combinación solo retrotraerá las filas que tengan datos coincidentes en ambos lados de la unión. Si no tenemos cuidado, esto podría dar como resultado que no se devolvieran datos válidos de nuestra tabla de autos si no había una fila correspondiente en esta subconsulta.

Ahora, mirando hacia atrás en la primera subconsulta, también hay algunas limitaciones. porque estamos retirando datos en una sola fila, SÓLO podemos retirar una fila de datos. Las subconsultas utilizadas 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 es necesario, pero a menudo es así como están escritos.

Entonces, antes de continuar, echemos un vistazo rápido a dónde más podemos usar una subconsulta. Podemos usarlo en la cláusula where : ahora, este ejemplo es un tanto artificial como en nuestra base de datos, hay mejores formas de obtener los siguientes datos, pero como solo es un ejemplo, echemos un vistazo:

 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 marca (la segunda columna solo se agrega para mostrar las marcas) que contiene la letra o en el nombre.

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

 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, a pesar de que la subconsulta devolvía las tres ID de marca, nuestra tabla de autos solo tenía entradas para dos de ellas.

En este caso, para más detalles, la subconsulta funciona 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) 

De nuevo, puede ver cómo una subconsulta frente a las 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. Existe un límite que depende de su base de datos, pero sin las funciones recursivas de un progtwigdor loco y maníaco, la mayoría de las personas nunca llegará a ese límite.
  • Puede colocar varias subconsultas en una sola consulta, algunas en la cláusula de select , algunas en la cláusula from y un par más en la cláusula where . Solo recuerde que cada una de las que ingresa hace que su consulta sea más compleja y probable que tomar más tiempo para ejecutar.

Si necesita escribir un código eficiente, puede ser beneficioso escribir la consulta de varias maneras y ver (ya sea cronometrando o utilizando un plan de explicación) que es la consulta óptima para obtener los resultados. La primera forma en que funciona no siempre es la mejor manera de hacerlo.

Parte 3 – Trucos y código eficiente

MySQL en () eficiencia

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

Una pregunta que veo venir un poco, es ¿Cómo obtengo filas que no coinciden de dos tablas y veo que la respuesta más comúnmente aceptada es algo como lo siguiente (basado en nuestra tabla de autos y marcas, que Holden ha enumerado como 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 , funcionará.

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

Sin embargo, no es eficiente en alguna base de datos. Here is a link to a Stack Overflow question asking about it, and here is an excellent in depth article if you want to get into the nitty gritty.

The short answer is, if the optimiser doesn’t handle it efficiently, it may be much better to use a query like the following to get non matched rows:

 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) 

Update Table with same table in subquery

Ahhh, another oldie but goodie – the old You can’t specify target table ‘brands’ for update in FROM clause .

MySQL will not allow you to run an update... query with a subselect on the same table. Now, you might be thinking, why not just slap it into the where clause right? But what if you want to update only the row with the max() date amoung a bunch of other rows? You can’t exactly do that in a where clause.

 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 

So, we can’t do that eh? Bueno no exactamente. There is a sneaky workaround that a surprisingly large number of users don’t know about – though it does include some hackery that you will need to pay attention to.

You can stick the subquery within another subquery, which puts enough of a gap between the two queries so that it will work. However, note that it might be safest to stick the query within a transaction – this will prevent any other changes being made to the tables while the query is running.

 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 

You can use the concept of multiple queries in the FROM keyword. Let me show you one example:

 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 

You can use as many tables as you want to. Use outer joins and union where ever it’s necessary, even inside table subqueries.

That’s a very easy method to involve as many as tables and fields.

Hopes this makes it find the tables as you’re reading through the thing:

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 | | +-------+-------------+------+-----+---------+----------------+