Curso de MariaDB Desde la Consola/JOINs

De WikiCabal
Saltar a: navegación, buscar

JOINs

JOINs son manera para consultar datos de dos o más tablas relacionadas.

CREATE TABLE Cursos ( ID smallint unsigned NOT NULL AUTO_INCREMENT,
                      Nombre varchar(50) NOT NULL,
                      PRIMARY KEY (ID)
);

MariaDB [Prueba1]> INSERT INTO Cursos values ( '', 'HTML5' ),
                                             ( '', 'CSS3' ),
                                             ( '', 'JavaScript' ),
                                             ( '', 'PHP' ),
                                             ( '', 'MariaDB' );
Query OK, 5 rows affected, 5 warnings (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 5

MariaDB [Prueba1]> insert into Cursos values ( '',  'Python' );
Query OK, 1 row affected, 1 warning (0.03 sec)

MariaDB [Prueba1]> SHOW CREATE TABLE Cursos;
+--------+-------------------------------------------------------------------------+
| Table  | Create Table                                                            |
+--------+-------------------------------------------------------------------------+
| Cursos | CREATE TABLE `Cursos` (
  `ID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `Nombre` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci      |
+--------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> DESCRIBE Cursos;
+--------+----------------------+------+-----+---------+----------------+
| Field  | Type                 | Null | Key | Default | Extra          |
+--------+----------------------+------+-----+---------+----------------+
| ID     | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| Nombre | varchar(50)          | NO   |     | NULL    |                |
+--------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

MariaDB [Prueba1]> SELECT * FROM Cursos;
+----+------------+
| ID | Nombre     |
+----+------------+
|  1 | HTML5      |
|  2 | CSS3       |
|  3 | JavaScript |
|  4 | PHP        |
|  5 | MariaDB    |
|  6 | Python     |
+----+------------+
5 rows in set (0.00 sec)

El número del curso se refiere a un tema en la table de Cursos

CREATE TABLE Usuarios ( ID smallint unsigned NOT NULL AUTO_INCREMENT,
                        Nombre varchar(30) NOT NULL,
                        Curso smallint unsigned,
                        PRIMARY KEY (ID),
                        KEY Curso (Curso),
                        CONSTRAINT Usuarios_ibfk_1 
                        FOREIGN KEY (Curso) REFERENCES Cursos (ID) );
Query OK, 0 rows affected (0.36 sec)


MariaDB [Prueba1]> SHOW CREATE TABLE Usuarios;
+----------+-------------------------------------------------------------------------+
| Table    | Create Table                                                            |
+----------+-------------------------------------------------------------------------+
| Usuarios | CREATE TABLE `Usuarios` (
  `ID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `Nombre` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `Curso` smallint(5) unsigned,
  PRIMARY KEY (`ID`),
  KEY `Curso` (`Curso`),
  CONSTRAINT `Usuarios_ibfk_1` FOREIGN KEY (`Curso`) REFERENCES `Cursos` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci       |
+----------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> DESCRIBE Usuarios;
+--------+----------------------+------+-----+---------+----------------+
| Field  | Type                 | Null | Key | Default | Extra          |
+--------+----------------------+------+-----+---------+----------------+
| ID     | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| Nombre | varchar(30)          | NO   |     | NULL    |                |
| Curso  | smallint(5) unsigned | YES  | UNI | NULL    |                |
+--------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [Prueba1]> INSERT INTO Usuarios values ( '', 'Ana', 2 ), 
                       ( '', 'Roberto', 1 ), ( '', 'Angel', 5 ), 
                       ( '', 'Laura', 3 ),  ( '', 'Ricardo', 4 );
Query OK, 5 rows affected, 5 warnings (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 5

MariaDB [Prueba1]> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column 'ID' at row 1 |
| Warning | 1366 | Incorrect integer value: '' for column 'ID' at row 2 |
| Warning | 1366 | Incorrect integer value: '' for column 'ID' at row 3 |
| Warning | 1366 | Incorrect integer value: '' for column 'ID' at row 4 |
| Warning | 1366 | Incorrect integer value: '' for column 'ID' at row 5 |
+---------+------+------------------------------------------------------+
5 rows in set (0.00 sec)

MariaDB [Prueba1]> INSERT INTO Usuarios values ( '', 'Mia', 2 );
Query OK, 1 row affected, 1 warning (0.08 sec)

MariaDB [Prueba1]> INSERT INTO Usuarios values ( '', 'Leo', 4 );
Query OK, 1 row affected, 1 warning (0.08 sec)

MariaDB [Prueba1]> INSERT INTO Usuarios ( ID, Nombre ) values ( '', 'Mauricio');
Query OK, 1 row affected, 1 warning (0.08 sec)


MariaDB [Prueba1]> SELECT * FROM Usuarios;
+----+---------+--------+
| ID | Nombre  | Curso  |
+----+---------+--------+
| 1  | Ana     |      2 |
| 1  | Roberto |      1 |
| 3  | Angel   |      5 |
| 4  | Laura   |      3 |
| 5  | Ricardo |      4 |
| 6  | Mia     |      2 |
| 7  | Leo     |      4 |
| 8  | Mauricio |  NULL |
+----+---------+--------+
7 rows in set (0.00 sec)

JOINs nos permite consultar estos datos en varios maneras.

INNER JOIN

La cláusula más frecuente es INNER JOIN. Esto produce
un conjunto de registros que coinciden con los usuarios 
y tables de Cursos, es decir, todos los Usuarios que están
inscritos en un curso:

MariaDB [Prueba1]> SELECT Usuarios.Nombre, Cursos.Nombre FROM `Usuarios`
                   INNER JOIN `Cursos` on 
                   Usuarios.Curso = Cursos.ID;
+---------+------------+
| Nombre  | Nombre     |
+---------+------------+
| Ana     | CSS3       |
| Roberto | HTML5      |
| Angel   | MariaDB    |
| Laura   | JavaScript |
| Ricardo | PHP        |
| Mia     | CSS3       |
| Leo     | PHP        |
+---------+------------+
7 rows in set (0.00 sec)

¿Qué pasa si necesitamos una lista de todos los estudiantes y sus 
cursos incluso si no estás inscrito en uno? Un LEFT JOIN produce el
conjunto de registros que coincide con cada entrada en la tabla
izquierda (Usuarios) sin importar ninguna entrada coincidente en la 
tabla derecha (Cursos):

MariaDB [Prueba1]> SELECT Usuarios.Nombre, Cursos.Nombre
       FROM `Usuarios`
       LEFT JOIN `Cursos` on Usuarios.Curso = Cursos.ID;
+----------+------------+
| Nombre   | Nombre     |
+----------+------------+
| Ana      | CSS3       |
| Roberto  | HTML5      |
| Angel    | MariaDB    |
| Laura    | JavaScript |
| Ricardo  | PHP        |
| Mia      | CSS3       |
| Leo      | PHP        |
| Mauricio | NULL       |
+----------+------------+
8 rows in set (0.00 sec)

RIGHT JOIN

Tal vez necesitamos una lista de todos los cursos y estudiantes 
incluso si nadie se ha inscrito? Un RIGHT JOIN produce un conjunto
de registros que coincide con cada entrada en la tabla de la 
derecha (Cursos) independientemente de cualquier entrada 
coincidente en la tabla de la izquierda (Usuarios):

MariaDB [Prueba1]> SELECT Usuarios.Nombre, Cursos.Nombre
       FROM `Usuarios`
       RIGHT JOIN `Cursos` on Usuarios.Curso = Cursos.ID;
+---------+------------+
| Nombre  | Nombre     |
+---------+------------+
| Roberto | HTML5      |
| Ana     | CSS3       |
| Mia     | CSS3       |
| Laura   | JavaScript |
| Ricardo | PHP        |
| Leo     | PHP        |
| Angel   | MariaDB    |
| NULL    | Python     |
+---------+------------+
7 rows in set (0.00 sec)

RGHT JOINs are rarely used since you can express the same result using a LEFT JOIN. This can be more efficient and quicker for the database to parse:
RGHT JOINs rara vez se utiliza ya que se puede expresar 
el mismo resultado utilizando un LEFT JOIN. Esto puede 
ser más eficiente y más rápido para la base de datos para 
analizar:

MariaDB [Prueba1]> SELECT Usuarios.Nombre, Cursos.Nombre 
                     FROM Cursos 
                   LEFT JOIN Usuarios on Usuarios.Curso = Cursos.ID;
+---------+------------+
| Nombre  | Nombre     |
+---------+------------+
| Roberto | HTML5      |
| Ana     | CSS3       |
| Mia     | CSS3       |
| Laura   | JavaScript |
| Ricardo | PHP        |
| Leo     | PHP        |
| Angel   | MariaDB    |
| NULL    | Python     |
+---------+------------+
7 rows in set (0.00 sec)

Podríamos contar el número de alumnos inscribiendos en cada curso:

MariaDB [Prueba1]> SELECT Cursos.Nombre, COUNT(Usuarios.Nombre)
                     FROM Cursos
                   LEFT JOIN Usuarios ON Usuarios.Curso = Cursos.ID
                   GROUP BY Cursos.ID;
+------------+------------------------+
| Nombre     | COUNT(Usuarios.Nombre) |
+------------+------------------------+
| HTML5      |                      1 |
| CSS3       |                      2 |
| JavaScript |                      1 |
| PHP        |                      2 |
| MariaDB    |                      1 |
| Python     |                      0 |
+------------+------------------------+
6 rows in set (0.00 sec)

OUTER JOIN (or FULL OUTER JOIN)

Nuestra última opción es la OUTER JOIN que devuelve todos 
los registros en ambas tablas, independientemente de cualquier 
partido. Cuando no existe coincidencia, el lado que falta 
contendrá NULL.

OUTER JOIN es menos útil que INNER LEFT o RIGHT y no se implementó en MySQL. 
Sin embargo, puede evitar esta restricción mediante la unión de 
un LEFT y RIGHT JOIN, por ejemplo,

SELECT Usuarios.Nombre, Cursos.Nombre 
         FROM Usuarios 
       LEFT JOIN Cursos 
         ON Usuarios.Curso = Cursos.ID 
UNION SELECT Usuarios.Nombre, Cursos.Nombre 
         FROM Usuarios 
       RIGHT JOIN Cursos 
         ON Usuarios.Curso = Cursos.ID;
+----------+------------+
| Nombre   | Nombre     |
+----------+------------+
| Ana      | CSS3       |
| Roberto  | HTML5      |
| Angel    | MariaDB    |
| Laura    | JavaScript |
| Ricardo  | PHP        |
| Mia      | CSS3       |
| Leo      | PHP        |
| Mauricio | NULL       |
| NULL     | Python     |
+----------+------------+
9 rows in set (0.00 sec)