Query to join two tables in MySQL

Question:

Help, please, just make a sample request.

There is the first table that has the columns спортсмен and серийный_номер_велосипеда .
The second table: серийный_номер_велосипеда , цвет , год_выпуска .

What will be the single request to select спортсмен , серийный_номер_велосипеда , цвет , год_выпуска ?

Answer:

Use a table join :

  1. If the list of bike serial numbers in both tables is the same (there are no such elements that are in one table, but not in the second), then you can use an inner join ( inner join ):

     SELECT s.sportsman, s.serial, b.color, b.year FROM sportsmen s JOIN bike b ON s.serial = b.serial

    Sample table on sqlfiddle

    The result of the query


    Or:

     SELECT s.sportsman, s.serial, b.color, b.year FROM sportsmen s, bike b WHERE s.serial = b.serial

    The result of the query


  1. If the list of bike serial numbers does not match, then when joining the tables using the inner join ( join ), you can lose the necessary rows, so to save all the rows in the left table use left join :

     SELECT s.sportsman, s.serial, b.color, b.year FROM sportsmen s LEFT JOIN bike b ON s.serial = b.serial

    Sample table on sqlfiddle

    The result of the query

Scroll to Top