Database kategorisi arşivi

Join kavramı, birden çok tablo ile işlem yaparken, tek query ile çekme, sıralama, filtreleme işlemleri yapabilmemizi sağlar.

Örneğimizde 3 adet tablomuz olacak. İlk tablomuzda oyuncu isimleri, ikinci tablomuzda maç isimleri, üçüncü tablomuzda ise kaydedilen goller olacak. (Gol atmayan futbolcu için üçüncü tabloda kayıt tutulmamakta.)

Sırasıyla tablolarımızı oluşturup içlerine verileri yerleştirelim.

İlki, futbolcu isimlerinin olduğu, ‘player‘ isimli tablo.

CREATE TABLE IF NOT EXISTS `player` (
  `player_id` int(11) NOT NULL AUTO_INCREMENT,
  `fullname` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`player_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=23 ;

INSERT INTO `player` (`player_id`, `fullname`) VALUES
(1, 'messi'),
(2, 'xavi'),
(3, 'iniesta'),
(4, 'abidal'),
(5, 'puyol'),
(6, 'yaya toure'),
(7, 'david silva'),
(8, 'balotelli'),
(9, 'aguero'),
(10, 'quaresma'),
(11, 'fernandes'),
(12, 'simao'),
(13, 'almeida'),
(14, 'pektemek'),
(15, 'hamit'),
(16, 'alanzinho'),
(17, 'altıntop'),
(18, 'henry'),
(19, 'van persie'),
(20, 'drogba'),
(21, 'lampard'),
(22, 'torres');

+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| player_id | int(11) | NO   | PRI | NULL    | auto_increment |
| fullname  | text    | NO   |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

mysql> SELECT *
    -> FROM player

+-----------+-------------+
| player_id | fullname    |
+-----------+-------------+
|         1 | messi       |
|         2 | xavi        |
|         3 | iniesta     |
|         4 | abidal      |
|         5 | puyol       |
|         6 | yaya toure  |
|         7 | david silva |
|         8 | balotelli   |
|         9 | aguero      |
|        10 | quaresma    |
|        11 | fernandes   |
|        12 | simao       |
|        13 | almeida     |
|        14 | pektemek    |
|        15 | hamit       |
|        16 | alanzinho   |
|        17 | altıntop    |
|        18 | henry       |
|        19 | van persie  |
|        20 | drogba      |
|        21 | lampard     |
|        22 | torres      |
+-----------+-------------+

İkincisi, maçların olduğu ‘matches‘ tablosu;

CREATE TABLE IF NOT EXISTS `matches` (
  `match_id` int(11) NOT NULL AUTO_INCREMENT,
  `match_name` text COLLATE utf8_unicode_ci NOT NULL,
  `match_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`match_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=13 ;

INSERT INTO `matches` (`match_id`, `match_name`, `match_date`) VALUES
(1, 'barcelona - city', '2012-01-18 21:37:04'),
(2, 'barcelona - arsenal', '2012-01-18 21:37:11'),
(3, 'barcelona - chelsea', '2012-01-18 21:37:19'),
(4, 'barcelona - beşiktaş', '2012-01-18 21:37:25'),
(5, 'barcelona - trabzon', '2012-01-18 21:37:32'),
(6, 'arsenal - chelsea', '2012-01-18 21:37:45'),
(7, 'arsenal - beşiktaş', '2012-01-18 21:37:51'),
(8, 'arsenal - trabzon', '2012-01-18 21:37:57'),
(9, 'chelsea - beşiktaş', '2012-01-18 21:38:09'),
(10, 'chelsea - trabzon', '2012-01-18 21:38:15'),
(11, 'beşiktaş - trabzon', '2012-01-18 21:38:25');

+------------+-----------+------+-----+-------------------+----------------+
| Field      | Type      | Null | Key | Default           | Extra          |
+------------+-----------+------+-----+-------------------+----------------+
| match_id   | int(11)   | NO   | PRI | NULL              | auto_increment |
| match_name | text      | NO   |     | NULL              |                |
| match_date | timestamp | NO   |     | CURRENT_TIMESTAMP |                |
+------------+-----------+------+-----+-------------------+----------------+

mysql> SELECT *
    -> FROM matches

+----------+----------------------+---------------------+
| match_id | match_name           | match_date          |
+----------+----------------------+---------------------+
|        1 | barcelona - city     | 2012-01-18 21:37:04 |
|        2 | barcelona - arsenal  | 2012-01-18 21:37:11 |
|        3 | barcelona - chelsea  | 2012-01-18 21:37:19 |
|        4 | barcelona - beşiktaş | 2012-01-18 21:37:25 |
|        5 | barcelona - trabzon  | 2012-01-18 21:37:32 |
|        6 | arsenal - chelsea    | 2012-01-18 21:37:45 |
|        7 | arsenal - beşiktaş   | 2012-01-18 21:37:51 |
|        8 | arsenal - trabzon    | 2012-01-18 21:37:57 |
|        9 | chelsea - beşiktaş   | 2012-01-18 21:38:09 |
|       10 | chelsea - trabzon    | 2012-01-18 21:38:15 |
|       11 | beşiktaş - trabzon   | 2012-01-18 21:38:25 |
+----------+----------------------+---------------------+

Son tablomuz, ‘score‘ tablosu, yani ‘player’ ve ‘matches’ tabloları arasında bağlantı kuracağımız tablo. (p2m)

CREATE TABLE IF NOT EXISTS `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `player_id` int(11) NOT NULL,
  `match_id` int(11) NOT NULL,
  `goals` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=17 ;

INSERT INTO `score` (`id`, `player_id`, `match_id`, `goals`) VALUES
(1, 1, 1, 2),
(2, 1, 2, 3),
(3, 2, 1, 1),
(4, 2, 5, 5),
(5, 5, 4, 1),
(6, 7, 1, 2),
(7, 10, 4, 2),
(8, 10, 11, 3),
(9, 12, 7, 1),
(10, 18, 2, 1),
(11, 18, 7, 2),
(12, 18, 8, 1),
(13, 20, 3, 1),
(14, 20, 6, 1),
(15, 21, 6, 1),
(16, 22, 10, 2);

+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| player_id | int(11) | NO   |     | NULL    |                |
| match_id  | int(11) | NO   |     | NULL    |                |
| goals     | int(11) | NO   |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

+----+-----------+----------+-------+
| id | player_id | match_id | goals |
+----+-----------+----------+-------+
|  1 |         1 |        1 |     2 |
|  2 |         1 |        2 |     3 |
|  3 |         2 |        1 |     1 |
|  4 |         2 |        5 |     5 |
|  5 |         5 |        4 |     1 |
|  6 |         7 |        1 |     2 |
|  7 |        10 |        4 |     2 |
|  8 |        10 |       11 |     3 |
|  9 |        12 |        7 |     1 |
| 10 |        18 |        2 |     1 |
| 11 |        18 |        7 |     2 |
| 12 |        18 |        8 |     1 |
| 13 |        20 |        3 |     1 |
| 14 |        20 |        6 |     1 |
| 15 |        21 |        6 |     1 |
| 16 |        22 |       10 |     2 |
+----+-----------+----------+-------+

Dikkat ederseniz, player tablosu ve score tablosunda ortak ‘player_id’ alanı, matches tablosu ve score tablosunda ortak ‘match_id’ alanı mevcuttur. Ortak tabloyu kullanarak maçlar ve futbolcularla ilgili çeşitli senaryolar kurup örnek sorgular verelim.

Öncelikle LEFT JOIN ve INNER JOIN arasındaki farkı ortaya koyan bir sorgu yapalım

mysql> SELECT *
    -> FROM player p
    -> LEFT JOIN score p2m
    -> ON (p2m.player_id = p.player_id);

Bu sorguyu yaptığımızda, açıkca görüldüğü üzere player tablosu ile score tablosu arasında, player_id kullanılarak left join yapılmıştır. Ve sonucu şu şekildedir;

+-----------+-------------+------+-----------+----------+-------+
| player_id | fullname    | id   | player_id | match_id | goals |
+-----------+-------------+------+-----------+----------+-------+
|         1 | messi       |    1 |         1 |        1 |     2 |
|         1 | messi       |    2 |         1 |        2 |     3 |
|         2 | xavi        |    3 |         2 |        1 |     1 |
|         2 | xavi        |    4 |         2 |        5 |     5 |
|         3 | iniesta     | NULL |      NULL |     NULL |  NULL |
|         4 | abidal      | NULL |      NULL |     NULL |  NULL |
|         5 | puyol       |    5 |         5 |        4 |     1 |
|         6 | yaya toure  | NULL |      NULL |     NULL |  NULL |
|         7 | david silva |    6 |         7 |        1 |     2 |
|         8 | balotelli   | NULL |      NULL |     NULL |  NULL |
|         9 | aguero      | NULL |      NULL |     NULL |  NULL |
|        10 | quaresma    |    7 |        10 |        4 |     2 |
|        10 | quaresma    |    8 |        10 |       11 |     3 |
|        11 | fernandes   | NULL |      NULL |     NULL |  NULL |
|        12 | simao       |    9 |        12 |        7 |     1 |
|        13 | almeida     | NULL |      NULL |     NULL |  NULL |
|        14 | pektemek    | NULL |      NULL |     NULL |  NULL |
|        15 | hamit       | NULL |      NULL |     NULL |  NULL |
|        16 | alanzinho   | NULL |      NULL |     NULL |  NULL |
|        17 | altıntop    | NULL |      NULL |     NULL |  NULL |
|        18 | henry       |   10 |        18 |        2 |     1 |
|        18 | henry       |   11 |        18 |        7 |     2 |
|        18 | henry       |   12 |        18 |        8 |     1 |
|        19 | van persie  | NULL |      NULL |     NULL |  NULL |
|        20 | drogba      |   13 |        20 |        3 |     1 |
|        20 | drogba      |   14 |        20 |        6 |     1 |
|        21 | lampard     |   15 |        21 |        6 |     1 |
|        22 | torres      |   16 |        22 |       10 |     2 |
+-----------+-------------+------+-----------+----------+-------+

Bazı futbolcuların ismi 2 kez geçmiştir (bunlar birden fazla maçta gol atanlar), bazı futbolcuların isimlerinin bulunduğu satırlarda ise diğer değerler NULL olarak gözükmektedir. Inner join yaptığımızda ise sonuç şöyledir;

mysql> SELECT *
    -> FROM player p
    -> INNER JOIN score p2m
    -> ON (p2m.player_id = p.player_id);

+-----------+-------------+----+-----------+----------+-------+
| player_id | fullname    | id | player_id | match_id | goals |
+-----------+-------------+----+-----------+----------+-------+
|         1 | messi       |  1 |         1 |        1 |     2 |
|         1 | messi       |  2 |         1 |        2 |     3 |
|         2 | xavi        |  3 |         2 |        1 |     1 |
|         2 | xavi        |  4 |         2 |        5 |     5 |
|         5 | puyol       |  5 |         5 |        4 |     1 |
|         7 | david silva |  6 |         7 |        1 |     2 |
|        10 | quaresma    |  7 |        10 |        4 |     2 |
|        10 | quaresma    |  8 |        10 |       11 |     3 |
|        12 | simao       |  9 |        12 |        7 |     1 |
|        18 | henry       | 10 |        18 |        2 |     1 |
|        18 | henry       | 11 |        18 |        7 |     2 |
|        18 | henry       | 12 |        18 |        8 |     1 |
|        20 | drogba      | 13 |        20 |        3 |     1 |
|        20 | drogba      | 14 |        20 |        6 |     1 |
|        21 | lampard     | 15 |        21 |        6 |     1 |
|        22 | torres      | 16 |        22 |       10 |     2 |
+-----------+-------------+----+-----------+----------+-------+

Açıkca görüldüğü gibi, INNER JOIN kullandığımızda NULL olan rowlar gelmemiştir.

Dikkat etmemiz gereken şey şudur.
Yaptığımız örnekte, gol atmayan futbolcuya hiç bir değer girmedik. Böylece LEFT JOIN yaptığımızda; player tablosundaki player_id, score tablosunda kendi karşılığı olan player_id bulamadığı için row komple NULL olarak döndü. Biz gol atmayan futbolcular için kayıt tutuyor olsaydık, goals sütunu bize 0 (sıfır) olarak dönerdi.

Inner Join kullandığımızda da 0 (sıfır) olan satırlar bize dönerdi. Çünkü NULL değil, sıfır değeri mevcut.

Mesela, henry’nin hangi maçlar gol attığını bulalım. Öncelikle player tablosundan henry’i alıp, score tablosuna taşımalıyız (player_id), oradaki değerle de (match_id) matches tablosundan maça ulaşmalıyız. Yani yapmamız gereken sorgu;

mysql> SELECT *
    -> FROM player p
    -> LEFT JOIN score p2m
    -> ON (p.player_id = p2m.player_id)
    -> LEFT JOIN matches m
    -> ON (m.match_id = p2m.match_id)
    -> WHERE p.fullname='henry';

+-----------+----------+------+-----------+----------+-------+----------+---------------------+---------------------+
| player_id | fullname | id   | player_id | match_id | goals | match_id | match_name          | match_date          |
+-----------+----------+------+-----------+----------+-------+----------+---------------------+---------------------+
|        18 | henry    |   10 |        18 |        2 |     1 |        2 | barcelona - arsenal | 2012-01-18 21:37:11 |
|        18 | henry    |   11 |        18 |        7 |     2 |        7 | arsenal - beşiktaş  | 2012-01-18 21:37:51 |
|        18 | henry    |   12 |        18 |        8 |     1 |        8 | arsenal - trabzon   | 2012-01-18 21:37:57 |
+-----------+----------+------+-----------+----------+-------+----------+---------------------+---------------------+

Hiç gol atılmayan maçları bulalım;

mysql> SELECT * FROM matches m
    -> LEFT JOIN score p2m
    -> ON (m.match_id = p2m.match_id)
    -> WHERE p2m.goals IS NULL;

+----------+--------------------+---------------------+------+-----------+----------+-------+
| match_id | match_name         | match_date          | id   | player_id | match_id | goals |
+----------+--------------------+---------------------+------+-----------+----------+-------+
|        9 | chelsea - beşiktaş | 2012-01-18 21:38:09 | NULL |      NULL |     NULL |  NULL |
+----------+--------------------+---------------------+------+-----------+----------+-------+

(Inner Join kullansaydık NULL olan satıra ulaşamazdık!)

barcelona – arsenal maçında 1′den fazla gol atan oyuncuları bulalım;

mysql> SELECT * FROM player p
    -> LEFT JOIN score p2m ON (p.player_id = p2m.player_id)
    -> LEFT JOIN matches m ON (m.match_id = p2m.match_id)
    -> WHERE m.match_name='barcelona - arsenal' AND p2m.goals>1;

+-----------+----------+------+-----------+----------+-------+----------+---------------------+---------------------+
| player_id | fullname | id   | player_id | match_id | goals | match_id | match_name          | match_date          |
+-----------+----------+------+-----------+----------+-------+----------+---------------------+---------------------+
|         1 | messi    |    2 |         1 |        2 |     3 |        2 | barcelona - arsenal | 2012-01-18 21:37:11 |
+-----------+----------+------+-----------+----------+-------+----------+---------------------+---------------------+