SQL/İçindekiler/Veri Sorgulama Dili (SELECT)

SELECT Sorgusu

değiştir

SELECT sorgularının tüm seçenekleriyle birlikte yazılımı aşağıdaki gibidir:

SELECT[ ALL| DISTINCT] <kolon ismi>[[ AS] <takma isim>][,[ ALL| DISTINCT] <kolon ismi>[[ AS] <takma isim>]]*
FROM <tablo>[[ AS] <takma isim>|[[ FULL| LEFT| RIGHT] OUTER| INNER] JOIN <tablo> ON <ifade>]
 [, <tablo>[[ AS] <takma isim>|[[ FULL| LEFT| RIGHT] OUTER| INNER] JOIN <tablo> ON <ifade>]]*

[WHERE <karşılaştırma ifadesi>[{ AND| OR} <karşılaştırma ifadesi>]*]
[GROUP BY <kolon ismi>[, <kolon ismi>]*
 [HAVING <karşılaştırma ifadesi>[{ AND| OR} <karşılaştırma ifadesi>]]*]
]
[ORDER BY <kolon ismi>[ ASC| DESC][, <kolon ismi>[ ASC| DESC]]*]
[FETCH FIRST <sayi> ROWS ONLY];

İlk Sorguyu Oluşturmak

değiştir

Şimdi toplanti isimli tabloyu oluşturalım:

toplanti
kod INTEGER
isim VARCHAR(20)
aciklama VARCHAR(255)
oncelik CHAR(1)
planlandi SMALLINT
tarih DATE
saat TIME
sure INTEGER
# ofisno INTEGER
pdf_raporu BLOB

...bilgileri girelim:

toplanti
kod isim aciklama oncelik planlandi tarih saat sure ofisno pdf_raporu
1 Planlama Proje planı hazırlanmalı.. A 1 2008-03-24 10:30:00 60 35 48644...846348
2 İlerleme Neler yaptık? C 1 2008-05-12 14:00:00 30 13 9862...15676
3 Değişim Projede neler değiştirilmeli? B 1 2008-06-03 9:30:00 90 41 34876...4846548
4 Sunum Proje Sunumu D 0 2008-09-11 15:30:00 120 27
5 Raporlama Projeye sonradan katılanlara yönelik açıklamalar B 1 2009-03-15 14:00:00 60 7 19739...37718
6 Öğrenme Yeni yazılım yüklendi. B 1 2009-09-21 16:00:00 120 11 785278...37528

Yukarıda oluşturduğumuz tablodaki verileri listeleyen ilk sorgumuzu yazalım :

  • Sorgu:
SELECT *
FROM toplanti;
  • Sonuç:
|-----------------------------------------------------------------------------------------------------------------------------------------------------------|
|kod        |isim         |aciklama                                         |oncelik  |planlandi |tarih      |saat     |sure     |ofisno    |pdf_raporu     |
|-----------|-------------|-------------------------------------------------|---------|----------|-----------|---------|---------|----------|---------------|
|1          |Planlama     |Proje planı hazırlanmalı..                       |A        |1         |2008-03-24 |10:30:00 |60       |35        |48644...846348 |
|2          |İlerleme     |Neler yaptık?                                    |C        |1         |2008-05-12 |14:00:00 |30       |13        |9862...15676   |
|3          |Değişim      |Projede neler değiştirilmeli?                    |B        |1         |2008-06-03 |9:30:00  |90       |41        |34876...4846548|
|4          |Sunum        |Proje Sunumu                                     |D        |0         |2008-09-11 |15:30:00 |120      |27        |NULL           |
|5          |Raporlama    |Projeye sonradan katılanlara yönelik açıklamalar |B        |1         |2009-03-15 |14:00:00 |60       |7         |19739...37718  |
|6          |Öğrenme      |Yeni yazılım yüklendi.                           |B        |1         |2009-09-21 |16:00:00 |120      |11        |785278...37528 |
|-----------------------------------------------------------------------------------------------------------------------------------------------------------|

Sonucun göründüğü biçim, istemci uygulamaya göre değişiklik gösterebilir. Bazılarında sadece metin olarak görünürken, bazılarında HTML veya başka bir uygulama nesnesi olarak görünebilir. SQL komutları ve ifadelerinin (SELECT, FROM vs.) büyük ya da küçük harfle yazılması önemli değildir. Genelde okunurluğunu artırmak için büyük harflerle yazılmaktadır.

SELECT ve FROM ifadeleri bir SELECT sorgusu için gerekli olan ifadelerdir:

  • FROM : sorgunun veriyi getireceği tabloların listesi,
  • SELECT : gelen verilerin listelenmesini sağlar.

WHERE ifadesi

değiştir

WHERE bir sorguda hangi kayıtların (satır) döneceği üzerinde etkisi vardır. Kayıtları filtreleme imkanı sağlayan bir şart ifadesinin yazılmasını sağlar. WHERE ifadesinden sonra sağlanan şart doğru veya yanlış olabilir. Bazı durumlarda bu şartlar bilinmeyen (NULL) sonuç üretebilir, SQL bu tür durumlarda da problemsiz çalışır. Aşağıdaki örnekte öncelik seviyesi B olan toplantıları listeler:

  • Sorgu:
SELECT *
FROM toplanti
WHERE toplanti.oncelik = 'B';
  • Sonuç:
|----------------------------------------------------------------------------------------------------------------------------------------------------|
|kod        |isim         |aciklama                                                 |oncelik  |planlandi |tarih       |saat     |sure     |ofisno |pdf_raporu         |
|-----------|-------------|---------------------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------|
|3          |Değişim      |Projede neler değiştirilmeli?                            |B        |1       |2008-06-03 |9:30:00  |90       |41        |34876...4846548 |
|5          |Raporlama    |Projeye sonradan katılanlara yönelik açıklamalar         |B        |1       |2009-03-15 |14:00:00 |60       |7         |19739...37718   |
|6          |Öğrenme      |Yeni yazılım yüklendi.                                   |B        |1       |2009-09-21 |16:00:00 |120      |11        |785278...37528  |
|----------------------------------------------------------------------------------------------------------------------------------------------------------------|

Tek bir tablodan veri alınırken tablo ismi yazılmayabilir.

Karşılaştırma İfadeleri

değiştir

Karşılaştırılan değerlerden birincisi ikincisine :

  • eşit olabilir: =
  • farklı olabilir: <>
  • küçük olabilir: <
  • küçük veya eşit olabilir : <=
  • büyük olabilir: >
  • büyük veya eşit olabilir: >=

Aşağıdaki sorgu toplanti tablosundaki toplantılardan öncelik seviyesi B olmayanları listeler:

  • Sorgu:
SELECT *
FROM toplanti
WHERE oncelik <> 'B';
  • Sonuç:
|----------------------------------------------------------------------------------------------------------------------------------------------------|
|kod        |isim         |aciklama                                   |oncelik  |planlandi |tarih      |saat     |sure     |ofisno    |pdf_reporu      |
|-----------|-------------|-------------------------------------------|---------|----------|-----------|---------|---------|----------|----------------|
|1          |Planlama     |Proje planı hazırlanmalı..                 |A        |1       |2008-03-24 |10:30:00 |60       |35        |48644...846348  |
|2          |İlerleme     |Neler yaptık?                              |C        |1       |2008-05-12 |14:00:00 |30       |13        |9862...15676    |
|4          |Sunum        |Proje Sunumu                               |D        |0       |2008-09-11 |15:30:00 |120      |27        |NULL            |
|----------------------------------------------------------------------------------------------------------------------------------------------------|

Operatörler (İşleçler)

değiştir

WHERE clause can have several conditions using the operators AND (bütün şartlar doğru olmalıdır) and OR (sadece bir şartın doğru olması yeterlidir). The operator OR is inclusive (birden fazla şart doğru olabilir). The order of evaluation can be indicated with brackets. NOT inverts a condition. The following query returns the reunions which have a B priority level and last more than an hour or which take place on 2008/05/12:

  • Query:
SELECT *
FROM reunion
WHERE (priority = 'B' AND NOT duration <= 60) OR date = '2008-05-12';
  • Result:
|----------------------------------------------------------------------------------------------------------------------------------------------------|
|id_reunion |name         |description                                |priority |planned |date       |hour     |duration |id_office |pdf_report      |
|-----------|-------------|-------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------|
|2          |Progress     |What we have done.                         |C        |1       |2008-05-12 |14:00:00 |30       |13        |9862...15676    |
|3          |Change       |What we need to change in the project.     |B        |1       |2008-06-03 |9:30:00  |90       |41        |34876...4846548 |
|6          |Learning     |A new software version has been installed. |B        |1       |2009-09-21 |16:00:00 |120      |11        |785278...37528  |
|----------------------------------------------------------------------------------------------------------------------------------------------------|

LIKE allows simplified regular expression matching. It can be applied on the text columns (CHAR, VARCHAR,...).

  • Alphanumerical characters only match identical text,
  • % is a wildcard that matches any text,
  • _ is a wildcard that matches any single character,

The following query returns the reunions which end with "ing" and which contain " the " in its description:

  • Query:
SELECT *
FROM reunion
WHERE name LIKE '%ing' AND description LIKE '% the %';
  • Result:
|----------------------------------------------------------------------------------------------------------------------------------------------------|
|id_reunion |name         |description                                |priority |planned |date       |hour     |duration |id_office |pdf_report      |
|-----------|-------------|-------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------|
|1          |Planning     |We need to plan the project.               |A        |1       |2008-03-24 |10:30:00 |60       |35        |48644...846348  |
|5          |Reporting    |Explanation to the new beginner.           |B        |1       |2009-03-15 |14:00:00 |60       |7         |19739...37718   |
|----------------------------------------------------------------------------------------------------------------------------------------------------|

BETWEEN and IN

değiştir

BETWEEN matches a range of values that can be numbers, dates or times. IN matches a list of allowed values. The following query returns the reunions which take place between 2008-04-01 and 2009-04-01 and have an A, B or D priority level:

  • Query:
SELECT *
FROM reunion
WHERE date BETWEEN '2008-04-01' AND '2009-04-01' AND priority IN ('A', 'B', 'D');
  • Result:
|----------------------------------------------------------------------------------------------------------------------------------------------------|
|id_reunion |name         |description                                |priority |planned |date       |hour     |duration |id_office |pdf_report      |
|-----------|-------------|-------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------|
|3          |Change       |What we need to change in the project.     |B        |1       |2008-06-03 |9:30:00  |90       |41        |34876...4846548 |
|4          |Presentation |Presentation of the project.               |D        |0       |2008-09-11 |15:30:00 |120      |27        |NULL            |
|5          |Reporting    |Explanation to the new beginner.           |B        |1       |2009-03-15 |14:00:00 |60       |7         |19739...37718   |
|----------------------------------------------------------------------------------------------------------------------------------------------------|

EXISTS is usually used with a subselect. This predicate is true if the list (i.e. the result set of a subselect) is not empty. This keyword allows to filter the returned rows using data that are not directly associated to the returned rows (i.e. they are not joined, not linked, not related... to the returned rows) so you can not use junction in this case. For instance, we want to retrieve all the reunions for which there is at least one reunion two times longer:

  • Query:
SELECT *
FROM reunion r1
WHERE EXISTS (
  SELECT r2.id_reunion
  FROM reunion r2
  WHERE r2.duration = r1.duration * 2
);
  • Result:
|----------------------------------------------------------------------------------------------------------------------------------------------------|
|id_reunion |name         |description                                |priority |planned |date       |hour     |duration |id_office |pdf_report      |
|-----------|-------------|-------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------|
|1          |Planning     |We need to plan the project.               |A        |1       |2008-03-24 |10:30:00 |60       |35        |48644...846348  |
|2          |Progress     |What we have done.                         |C        |1       |2008-05-12 |14:00:00 |30       |13        |9862...15676    |
|5          |Reporting    |Explanation to the new beginner.           |B        |1       |2009-03-15 |14:00:00 |60       |7         |19739...37718   |
|----------------------------------------------------------------------------------------------------------------------------------------------------|

The duration of another reunion is used in this query whereas there is no join, no link and no relationship between the two rows. This condition can not be done without EXISTS. Note that the subselect uses the alias r1 whereas this alias is defined in the main query.

EXISTS is also used to match a lack of data. Let's remember the employee table and the members table:

employee
id_employee firstname lastname phone mail
1 Big BOSS 936854270 big.boss@company.com
2 John DOE 936854271 john.doe@company.com
3 Linus TORVALDS 936854272 linus.torvalds@company.com
4 Jimmy WALES 936854273 jimmy.wales@company.com
5 Larry PAGE 936854274 larry.page@company.com
6 Max THE GOOGLER 936854275 max.the-googler@company.com
7 Jenny THE WIKIPEDIAN 936854276 jenny.the-wikipedian@company.com
members
# id_employee # id_project
3 2
2 1
4 3
5 1
2 3
6 1
7 3

The following query returns the employees who are not linked to any project (i.e. the ones there is no relationship for them in the members table):

  • Query:
SELECT *
FROM employees e
WHERE NOT EXISTS (
  SELECT m.id_employee
  FROM members m
  WHERE m.id_employee = e.id_employee
);
  • Result:

|------------------------------------------------------------------|
|id_employee |firstname |lastname |phone     |mail                 |
|------------|----------|---------|----------|---------------------|
|1           |Big       |BOSS     |936854270 |big.boss@company.com |
|------------------------------------------------------------------|

IS NULL tests if a column is filled. It is often used for foreign key columns.

FROM clause

değiştir

The FROM clause defines the tables that are used for the query but it can also join tables. A JOIN builds a super table with the columns of two tables to be used for the query. To explain what a join is, we consider two archaic tables without primary keys nor foreign keys:

table_1
common_value specific_value_1
red 9999
grey 6666
white 0000
purple 7777
purple 2222
black 8888
table_2
common_value specific_value_2
green HHHHHH
yellow PPPPPP
black FFFFFF
red OOOOOO
red LLLLLL
blue RRRRRR

We want to associate values from columns of different tables matching values on a given column in each table.

FULL OUTER JOIN

değiştir

A JOIN is made matching a column on a table to a column on the other table. After a FULL OUTER JOIN, for a given value (red), for a given row with this value on one table ([ red | 9999 ]), one row is created for each row that matches on the other table ([ red | OOOOOO ] and [ red | LLLLLL ]). If a value exists in only one table, then a row is created and is completed with NULL columns.

FROM table_1 FULL OUTER JOIN table_2 ON table_1.common_value = table_2.common_value
common_value specific_value_1 specific_value_2
red 9999 OOOOOO
red 9999 LLLLLL
grey 6666 NULL
white 0000 NULL
purple 7777 NULL
purple 2222 NULL
black 8888 FFFFFF
green NULL HHHHHH
yellow NULL PPPPPP
blue NULL RRRRRR

RIGHT OUTER JOIN

değiştir

The RIGHT OUTER JOIN is like the FULL OUTER JOIN but it doesn't create row for values that don't exist on the left table.

FROM table_1 RIGHT OUTER JOIN table_2 ON table_1.common_value = table_2.common_value
common_value specific_value_1 specific_value_2
red 9999 OOOOOO
red 9999 LLLLLL
black 8888 FFFFFF
green NULL HHHHHH
yellow NULL PPPPPP
blue NULL RRRRRR

LEFT OUTER JOIN

değiştir

The LEFT OUTER JOIN is like the FULL OUTER JOIN but it doesn't create row for values that don't exist on the right table.

FROM table_1 LEFT OUTER JOIN table_2 ON table_1.common_value = table_2.common_value
common_value specific_value_1 specific_value_2
red 9999 OOOOOO
red 9999 LLLLLL
grey 6666 NULL
white 0000 NULL
purple 7777 NULL
purple 2222 NULL
black 8888 FFFFFF

INNER JOIN

değiştir

The INNER JOIN is like the FULL OUTER JOIN but it creates row only for values that exist on both the left table and the right table.

FROM table_1 INNER JOIN table_2 ON table_1.common_value = table_2.common_value
common_value specific_value_1 specific_value_2
red 9999 OOOOOO
red 9999 LLLLLL
black 8888 FFFFFF

The FROM clause can declare several tables, separated by , and aliases can be defined for table name with the keyword AS, which allows the user to make several joins with the same tables. The following query is equivalent to the INNER JOIN above:

  • Query:
SELECT *
FROM table_1 AS t1, table_2 AS t2
WHERE t1.common_value = t2.common_value

The keyword AS can be omitted.

SELECT clause

değiştir

The SELECT clause doesn't influence the data processed by the query but the data returned to the user. * return all the data processed after joining and filtering. Otherwise, the SELECT clause lists expressions separated by ,.

The expressions can be a table name, a table name and a column name separated by a dot or simply a column name if it is not ambiguous. The SELECT clause also allows evaluated expressions like addition, subtraction, concatenation, ... An expression can be followed by an alias with the keyword AS. The keyword AS can be omitted.

Here is an example:

  • Query:
SELECT reunion.id_reunion, concat(name, ' : ', reunion.description) n, priority AS p, planned * 10 AS plan, duration + 10 AS reunion_length
FROM reunion;
  • Result:
|-------------------------------------------------------------------------------------------|
|id_reunion |n                                                     |p |plan |reunion_length |
|-----------|------------------------------------------------------|--|-----|---------------|
|1          |Planning : We need to plan the project.               |A |10   |70             |
|2          |Progress : What we have done.                         |C |10   |40             |
|3          |Change : What we need to change in the project.       |B |10   |100            |
|4          |Presentation : Presentation of the project.           |D |0    |130            |
|5          |Reporting : Explanation to the new beginner.          |B |10   |70             |
|6          |Learning : A new software version has been install... |B |10   |130            |
|-------------------------------------------------------------------------------------------|

The expressions can be also the following aggregation functions:

  • count(*): the count of rows returned,
  • max(<column_name>): the greatest value of the column,
  • min(<column_name>): the lowest value of the column.

Here is a new example:

  • Query:
SELECT count(*) * 10 AS c, max(date) AS latest_date, min(reunion.date) oldest_date
FROM reunion;
  • Result:
|-----------------------------|
|c  |latest_date |oldest_date |
|---|------------|------------|
|60 |2009-09-21  |2008-03-24  |
|-----------------------------|

ORDER BY clause

değiştir

The ORDER BY clause sorts the rows returned by the query by one or several columns. The sort is done with the first column mentioned. The second column is used to sort the rows which have the same value in the first column and so on. The keywords ASC or DESC can be added after each column. ASC indicates an ascending sort. DESC indicates a descending sort. Default is a descending sort. Let's do two simple requests, the first sorting by only one column and the second sorting by two columns:

  • Query:
SELECT *
FROM reunion
ORDER BY priority ASC;
  • Result:
|----------------------------------------------------------------------------------------------------------------------------------------------------|
|id_reunion |name         |description                                |priority |planned |date       |hour     |duration |id_office |pdf_report      |
|-----------|-------------|-------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------|
|1          |Planning     |We need to plan the project.               |A        |1       |2008-03-24 |10:30:00 |60       |35        |48644...846348  |
|3          |Change       |What we need to change in the project.     |B        |1       |2008-06-03 |9:30:00  |90       |41        |34876...4846548 |
|5          |Reporting    |Explanation to the new beginner.           |B        |1       |2009-03-15 |14:00:00 |60       |7         |19739...37718   |
|6          |Learning     |A new software version has been installed. |B        |1       |2009-09-21 |16:00:00 |120      |11        |785278...37528  |
|2          |Progress     |What we have done.                         |C        |1       |2008-05-12 |14:00:00 |30       |13        |9862...15676    |
|4          |Presentation |Presentation of the project.               |D        |0       |2008-09-11 |15:30:00 |120      |27        |NULL            |
|----------------------------------------------------------------------------------------------------------------------------------------------------|
  • Query:
SELECT *
FROM reunion
ORDER BY priority ASC, duration DESC;
  • Result:
|----------------------------------------------------------------------------------------------------------------------------------------------------|
|id_reunion |name         |description                                |priority |planned |date       |hour     |duration |id_office |pdf_report      |
|-----------|-------------|-------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------|
|1          |Planning     |We need to plan the project.               |A        |1       |2008-03-24 |10:30:00 |60       |35        |48644...846348  |
|6          |Learning     |A new software version has been installed. |B        |1       |2009-09-21 |16:00:00 |120      |11        |785278...37528  |
|3          |Change       |What we need to change in the project.     |B        |1       |2008-06-03 |9:30:00  |90       |41        |34876...4846548 |
|5          |Reporting    |Explanation to the new beginner.           |B        |1       |2009-03-15 |14:00:00 |60       |7         |19739...37718   |
|2          |Progress     |What we have done.                         |C        |1       |2008-05-12 |14:00:00 |30       |13        |9862...15676    |
|4          |Presentation |Presentation of the project.               |D        |0       |2008-09-11 |15:30:00 |120      |27        |NULL            |
|----------------------------------------------------------------------------------------------------------------------------------------------------|

GROUP BY clause

değiştir

The GROUP BY clause is used for aggregation operations. It gathers the rows into groups, for instance, all the rows that have the same value in a given column. After gathering rows into groups, any aggregation operation is applied on each group instead of a unique big group of rows. As a consequence, an aggregation operation will return as many result as the number of groups. Groups can be formed with all the rows that have the same value for a given column or the same combination of values for several given columns. For instance, we want to know the number of reunions for each type of priority:

  • Query:
SELECT count(*) as number, priority
FROM reunion
GROUP BY priority;
  • Result:
|-----------------|
|number |priority |
|-------|---------|
|1      |A        |
|3      |B        |
|1      |C        |
|1      |D        |
|-----------------|

Due to the GROUP BY clause, the aggregation function count(*) doesn't return a global count but a count for each priority level (A, B, C and D).

  • Query:
SELECT count(*) as number, planned, duration
FROM reunion
GROUP BY planned, duration;
  • Result:
|--------------------------|
|number |planned |duration |
|-------|--------|---------|
|1      |0       |120      |
|1      |1       |30       |
|2      |1       |60       |
|1      |1       |90       |
|1      |1       |120      |
|--------------------------|

Note that there are four groups with 1 for the column planned and there are two groups with 120 for the column duration. However, you can see that there is no group with the same combination of values from the two columns.

HAVING clause

değiştir

The HAVING clause is used with the GROUP BY clause. The HAVING clause contains a predicate and removes from the returned rows the groups for which the predicate is false. For example, we want to retrieve only the priorities for which there are at least two reunions with the same priority level:

  • Query:
SELECT priority
FROM reunion
GROUP BY priority
HAVING count(*) > 1;
  • Result:
|---------|
|priority |
|---------|
|B        |
|---------|

FETCH FIRST clause

değiştir

The FETCH FIRST clause is used to limit the number of returned rows. Only the first rows are returned. The number of returned rows is the number indicated in the clause.

  • Query:
SELECT *
FROM reunion
FETCH FIRST 4 ROWS ONLY;
  • Result:
|----------------------------------------------------------------------------------------------------------------------------------------------------|
|id_reunion |name         |description                                |priority |planned |date       |hour     |duration |id_office |pdf_report      |
|-----------|-------------|-------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------|
|1          |Planning     |We need to plan the project.               |A        |1       |2008-03-24 |10:30:00 |60       |35        |48644...846348  |
|2          |Progress     |What we have done.                         |C        |1       |2008-05-12 |14:00:00 |30       |13        |9862...15676    |
|3          |Change       |What we need to change in the project.     |B        |1       |2008-06-03 |9:30:00  |90       |41        |34876...4846548 |
|4          |Presentation |Presentation of the project.               |D        |0       |2008-09-11 |15:30:00 |120      |27        |NULL            |
|----------------------------------------------------------------------------------------------------------------------------------------------------|

This clause is often used not to return useless rows for test or to improve the performance.

Now you can explore all the data of an already existing database.