SQL join example

example.sql

/**
 * Пользователь
 * Без внешного ключа, по этому нет проверки целосности
 */
CREATE TABLE IF NOT EXISTS `user` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255),
  `city_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY `pk_id`(`id`),
  INDEX `city_id_idx` (`city_id` ASC) -- Индекс. Для быстрой выборки
) ENGINE = InnoDB;

/**
 * Города
 */
CREATE TABLE IF NOT EXISTS `city` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY `pk_id`(`id`)
) ENGINE = InnoDB;

/**
 * Тестовые данные
 */
INSERT INTO `city`(`name`) VALUES
('Ophioglossales'),
('lunulet'),
('dumosity'),
('inhaler'),
('goodyish'),
('anchorer'),
('scuppernong'),
('Teutondom'),
('hypertely'),
('moody');

INSERT INTO `user`(`name`, `city_id`) VALUES
('Delores Bowcutt', 1),
('Rosa Carriker', 2),
('Mirian Sutphen', 1),
('Andria Sulyma', 3),
('Vonda Zoumis', 2),
('Regena Schouweiler', 5),
('Jenine Scullen', 4);


/**
 * Выборка
 */
SELECT
    `u`.`id`,
    `u`.`name`,
    `c`.`name` AS `city`,
    `c`.`id` -- Если нужно
  FROM `user` AS `u`
  	INNER JOIN `city` AS `c` ON ( `c`.`id` = `u`.`city_id` );