10のステップでSQLを理解する

SQLは、慣れ親しんだコマンドライン言語やオブジェクト指向プログラミング言語、さらには関数型言語とも全く異なる方法で動作する数少ない宣言型言語の1つです。

私は毎日SQLを書き、オープンソース・ソフトウェアのjOOQで使っています。そこで、まだSQLに頭を悩ませている皆さんに、SQLの素晴らしさを紹介したいと思い、今回は特別に以下のような読者向けに記事を書きました：

1、SQLを業務で使用しているが、十分に理解していない人。

2、SQLを使いこなせるが、その構文論理を理解していない人。

3.SQLを人に教えたい人。

[]

この記事ではSELECT構文に焦点を当てますが、その他のDMLについては他の記事で取り上げます。

SQLを完全に理解するための10の簡単なステップ

1. SQLは宣言型言語です。

まず頭に叩き込むべきは、「宣言」という概念です。 SQL言語は、生のデータからどのような結果を得たいかというパラダイムをコンピュータに宣言しているのであって、コンピュータに結果を得る方法を教えているのではありません。素晴らしいことだと思いませんか？

SELECT first_name, last_name FROM employees WHERE salary > 100000 

上記の例は理解しやすく、これらの従業員記録がどこから来たかは気にしません。必要なのは、高い報酬を得ている人のデータだけです。

どこで学んだのですか？

SQL言語がとてもシンプルだとしたら、なぜ人々は「SQLを怖がる」のでしょうか？主な理由は、人々が無意識のうちに命令型プログラミングの観点から考えているからです。コンピュータ、これをやって、あれをやって、条件Aと条件Bが満たされたらチェックする」という感じです。例えば、変数でパラメータを渡したり、ループ文を使ったり、繰り返し処理をしたり、関数を呼び出したりするのは、すべてこの命令型プログラミングの考え方の一部です。

2.SQLの構文は構文順に実行されません。

多くの人を混乱させるSQL文の特徴の1つは、SQL文の実行順序が文の構文順序と一致しないことです：

• select[distinct]
• FROM
• WHERE
• GROUP BY
• HAVING
• UNION
• ORDER BY

理解を容易にするために、上記のSQL構文構造のすべてを列挙したわけではありませんが、SQL文の構文順序とその実行順序がまったく異なることを示すには十分です：

• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT
• DISTINCT
• UNION
• ORDER BY

SQL文の実行順序について、注目すべき点が3つあります：

1. FROMはSQL文実行の***ステップであり、SELECTではありません。最初に起こることは、そのようなデータを操作するために、ディスクからメモリにデータをロードすることです。"最初に起こることは、そのようなデータを操作するために、ディスクからメモリにデータをロードすることです。"とありますが、そうではありません。データベースなどでは、データはハードディスクからデータバッファに取り出されて操作されます)。

2, SELECTはほとんどのステートメントが実行された後、厳密にはFROMとGROUP BYの後に実行されます。このことを理解することは非常に重要であり、SELECTエイリアスフィールドのWHEREを判定条件として使用することができない理由です。

SELECT A.x + A.y AS z 
FROM A 
WHERE z = 10 -- z SELECTが***ステートメントを実行する！ 

エイリアスzを再利用したい場合、2つの選択肢があります。zで表される式をもう一度書き直すか：

SELECT A.x + A.y AS z 
FROM A 
WHERE (A.x + A.y) = 10 

エイリアスの再利用を避けるために、派生テーブル、汎用データ式、ビューに頼ることもできます。以下の例を参照してください。

3.UNIONは、構文的にも実行順序的にも、常にORDER BYの前に来ます。多くの人は、すべてのUNIONセグメントはORDER BYを使用してソートできると考えていますが、SQL言語標準と各データベースにおけるSQLの実行の違いによると、これは真実ではありません。データベースによっては、SQL文でサブクエリや派生テーブルをソートすることができますが、これはUNION操作の後にソートされた順序が残るという意味ではありません。

注意: すべてのデータベースでSQL文の解析方法が同じとは限りません。例えば、MySQL、PostgreSQL、SQLiteは、上記のポイント2で説明したような同じ方法では実行しません。

何を学びましたか？

すべてのデータベースが上記の方法でSQLを実行するわけではありません。教訓は、SQL文の構文上の順番と実行される順番は違うということを常に覚えておくことです。SQL文の構文上の順番と実行される順番の違いを覚えておけば、SQLのよくある問題を簡単に理解することができます。

もちろん、構文的順序がステートメントの実行順序を直接反映するように設計されていれば、言語は非常にプログラマーフレンドリーであり、このプログラミング言語レベルの設計哲学はマイクロソフトによってLINQ言語に適用されています。

#p#

3.SQL言語の核心はテーブルへの参照です。

SQL文の構文順序や実行順序が異なるため、多くの生徒はSELECTのフィールド情報がSQL文の核心であると考えるでしょう。実際には、本当の核心はテーブルへの参照にあります。

FROM文は標準SQLに従って定義されています：

<from clause> ::= FROM <table reference> [ { <comma> <table reference> }... ] 

FROM文の "出力 "は、あるディメンジョンで参照されるすべてのテーブルの結合から得られる結合テーブルです。これについては後で分析します：

FROM a, b 

テーブルaに3つのフィールドがあり、テーブルbに5つのフィールドがある場合、"出力テーブル "には8つのフィールドがあります。テーブルaに3つのフィールドがあり、テーブルbに5つのフィールドがある場合、"出力テーブル "には8つのフィールドがあります。

このユニオン・テーブルのデータは a*b で、これは a と b のデカルト積です。言い換えると、テーブルaのすべてのデータは、テーブルbのすべてのデータと対にならなければなりません。テーブルaに3つのエントリがあり、テーブルbに5つのエントリがある場合、ユニオン・テーブルは15のエントリを持つことになります。

FROM 出力は WHERE ステートメントでフィルタリングされ、GROUP BY ステートメントで処理されて新しい出力になります。これについては後述します。

集合論的な観点から見れば、データベースのテーブルはデータ要素の関係の集合であり、SQL文はそれぞれ1つ以上の関係を変更し、データ要素の新しい関係をもたらします。

何を学びましたか？

問題を考えるときは、SQL文のデータがどのような変化を "パイプライン "しているのかを理解しやすいように、テーブルの視点から考えましょう。

4.テーブルへの柔軟な参照は、SQLステートメントをより強力にします。

テーブルを柔軟に参照することで、SQL文はより強力になります。簡単な例はJOINの使用です。厳密に言えば、JOIN文はSELECTの一部ではなく、特別な種類のテーブル参照文です。 SQL言語標準では、テーブル結合を以下のように定義しています：

<table reference> ::= 
    <table name> 
  | <derived table> 
  | <joined table> 

の例を見てみましょう：

FROM a, b 

a 下表のような接続が考えられます：

a1 JOIN a2 ON a1.id = a2.id 

それを例に出すとこうなります：

FROM a1 JOIN a2 ON a1.id = a2.id, b 

カンマを使用して結合テーブルと別のテーブルを結合することは一般的ではありませんが、可能です。結果はa1+a2+bフィールドを持つテーブルになります。

SQL文での派生テーブルの使用は、後述するテーブル結合よりもさらに強力です。

何を学びましたか？

テーブル参照で考えると、SQL文でデータがどのように処理されるかを理解しやすくなり、複雑なテーブル参照が何をするのかを理解するのに役立ちます。

さらに重要なことは、JOINは結合されたテーブルを構築するためのキーワードであり、SELECT文の一部ではないということを理解することです。INSERT、UPDATE、DELETEでJOINを使用できるデータベースもあります。

5. SQL文でのテーブル結合の推奨使用法

先ほどの文章を少し見てみましょう：

FROM a, b 

上級のSQLプログラマーは、テーブルの結合にJOINの代わりにカンマを使わないようにしてください。

SQLステートメントを簡略化するためにカンマを使用することは、時に思考を混乱させることがあります：

FROM a, b, c, d, e, f, g, h 
WHERE a.a1 = b.bx 
AND a.a2 = c.c1 
AND d.d1 = b.bc 
-- etc... 

JOINステートメントを使う利点は簡単です：

• セキュリティ JOINと結合されるテーブルが非常に近いため、エラーを避けることができます。
• より多くの接続方法として、JOIN文は外側joinと内側joinを区別することができます。

何を学びましたか？

テーブルの結合にはできるだけJOINを使用し、FROMの後にカンマを使用しないことを忘れないでください。

#p#

6.SQL文のさまざまな結合操作

SQL文のテーブル結合には基本的に5つのタイプがあります：

• EQUI JOIN
• SEMI JOIN
• ANTI JOIN
• CROSS JOIN
• DIVISION

エキジョイン

これはJOIN操作の最も一般的なタイプで、2つのタイプのJOINで構成されています：

• INNER JOIN
• OUTER JOIN

この違いは、例を挙げるのが一番わかりやすいでしょう：

-- This table reference contains authors and their books. 
-- There is one record for each book and its author. 
-- authors without books are NOT included 
author JOIN book ON author.id = book.author_id 
  
-- This table reference contains authors and their books 
-- There is one record for each book and its author. 
-- ... OR there is an "empty" record for authors without books 
-- ("empty" meaning that all book columns are NULL) 
author LEFT OUTER JOIN book ON author.id = book.author_id 

セミ・ジョイン

-- Using IN 
FROM author 
WHERE author.id IN (SELECT book.author_id FROM book) 
  
-- Using EXISTS 
FROM author 
WHERE EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id) 

どのような場合にINを使い、どのような場合にEXISTSを使うかについて厳密なルールはありませんが、これらは知っておくべきことです：

• INEXISTSよりも読みやすい
• EXISTS INよりも表現力が強い
• 両者の性能に差はなし

-- Find only those authors who also have books 
SELECT DISTINCT first_name, last_name 
FROM author 
JOIN book ON author.id = book.author_id 

これは、以下の理由から、ひどい書き方です：

• SQL文のパフォーマンスが低い：重複排除処理では、データベースがハードドライブからメモリにデータを繰り返し読み込む必要があるためです。
• 今は問題ないかもしれませんが、SQL文が複雑になるにつれて、正しい結果を得るためにSQL文を軽視することは非常に難しくなります。

(///--------ql/)。

アンチジョイン

-- Using IN 
FROM author 
WHERE author.id NOT IN (SELECT book.author_id FROM book) 
  
-- Using EXISTS 
FROM author 
WHERE NOT EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id) 

このブログの記事では、NULLが発生したNOT INの使用について説明します、この記事のテーマから少し逸脱があるため、詳細には行きませんが、興味のある学生は、次の記事を読むことができます。

(///------es/)。

クロスジョイン

つまり、***テーブルの各データは、それぞれ2番目のテーブルの各データに対応します。これがFROM文でのカンマの使い方です。実際のアプリケーションでCROSS JOINを使用する場所はほとんどありませんが、使用する場合はこのようにSQL文で表現することができます：

-- Combine every author with every book 
author CROSS JOIN book 

#p#

ディビジョン

DIVISIONは実に奇妙です。要するに、JOINが乗算操作であるならば、DIVISIONはJOINの逆です。DIVISIONの関係をSQLで表現するのは難しいですし、これは初心者向けのガイドなので、DIVISIONを説明することは目的外です。しかし、もし興味があれば、以下の3つの記事をチェックしてください。

(////-----oq/)

(/_#on)

(https://.-.//--/-------on/).

おすすめの本 →_→ 「wiki/View_%28SQL%29ステートメントを説明する図を描く

何を学びましたか？

多くを学びました！頭の中でもう一度確認してみましょう。 SQLはテーブルへの参照であり、JOINはテーブルへの複雑な参照方法です。しかし、SQL言語の表現と実際に必要な論理関係には違いがあり、すべての論理関係が対応するJOIN操作で見つかるわけではないので、今後SQL文を書く際に適切なJOIN操作を選択できるように、より多くの関係論理を蓄積して学ぶ必要があります。

7.SQLの変数としての派生テーブル

ここで、SQLは宣言型言語であり、SQL文に変数を含めることはできないことを学びました。しかし、変数と似たようなステートメントを書くことができ、これらは派生テーブルと呼ばれます：

明確にしておくと、いわゆる派生テーブルは括弧内のサブクエリです：

-- A derived table 
FROM (SELECT * FROM author) 

派生テーブルはSQLロジックによる問題を効果的に回避することができます。例えば、SELECT文とWHERE文を使ったクエリの結果を再利用したい場合は、このように記述します：

-- Get authors' first and last names, and their age in days 
SELECT first_name, last_name, age 
FROM ( 
  SELECT first_name, last_name, current_date - date_of_birth age 
  FROM author 
) 
-- If the age is greater than 10000 days 
WHERE age > 10000 

注意: データベースによっては、また標準SQL:1990では、派生テーブルは次のレベルであるgeneric table文に分類されています。これにより、1つのSELECT文で派生テーブルを複数回再利用することができます。上記の例は以下の文と同等です：

WITH a AS ( 
  SELECT first_name, last_name, current_date - date_of_birth age 
  FROM author 
) 
SELECT * 
FROM a 
WHERE age > 10000 

もちろん、" a " 用に別のビューを作成して、この派生テーブルをより広いコンテキストで再利用することもできます。詳細については、以下の記事(/ew_%29)を参照してください。

何を学びましたか？

SQL文はフィールドを参照するものではなく、テーブルを参照するものです。このことをよく理解し、派生テーブルやより複雑なステートメントを使用することを恐れないでください。

8、SQL文のGROUP BYは、テーブルの参照に対する操作です。

もう一度FROM文を思い出してみましょう：

FROM a, b 

上記のステートメントに GROUP BY を適用します：

GROUP BY A.x, A.y, B.z 

上記のステートメントの結果は、3つのフィールドを持つ新しいテーブルへの参照です。GROUP BYを適用すると、SELECTの後に集約関数を使用していない列がGROUP BYの後に表示されます。

• 注意点として、他のフィールドでも集約関数を使用することができます：

SELECT A.x, A.y, SUM(A.z) 
FROM A 
GROUP BY A.x, A.y 

• また、MySQL がこの標準に準拠していないことも注目に値します。しかし、MySQL に惑わされないでください。 GROUP BY はテーブルの参照方法を変更します。SELECTでフィールドを参照し、GROUP BYでこのようにグループ化することができます。

何を学びましたか？

GROUP BYもまた、テーブル参照を操作し、新しい参照方法に変換します。

#p#

9.SQL文のSELECTは基本的に関係のマッピングです。

私は個人的に、特にリレーショナル代数で使われる "マッピング "という言葉が好きです。テーブルへの参照を確立したら、それを修正し、変形し、別のモデルに段階的にマッピングすることができます。 SELECT文は "プロジェクター "のようなもので、あるロジックに従ってソーステーブルのデータをターゲットテーブルのデータに変換する関数と考えることができます。

SELECT文を使えば、各フィールドを操作し、複雑な式を通して必要なデータを生成することができます。

SELECT文にはいくつかの特別なルールがあります：

1. テーブル参照から派生したフィールドしか使用できません；
2. GROUP BY文がある場合は、GROUP BY文に続くフィールドまたは集約関数のみを使用できます；
3. ステートメントに GROUP BY がない場合、集約関数の代わりにウィンドウ関数を使用できます；
4. ステートメントにGROUP BYがない場合、集約関数と他の関数を同時に使用することはできません；
5. 通常の関数を集約関数にカプセル化する方法はいくつかあります；
6. ......

より複雑なルールの中には、別の記事が必要なほど多くのものがあります。例えば、GROUP BYを使用しないSELECT文では、なぜ通常の関数と集約関数の両方を使用できないのですか？

理由は以下の通り：

1. 直感的に、このやり方は論理的におかしい。
2. 直感で納得できなくても、構文で納得できるはずです。 SQL : 1999標準ではGROUPING SETが導入され、SQL : 2003標準ではGROUP BY()が導入されました。文の中に集約関数があり、明示的なGROUP BY文がない場合、指定されていない空のGROUPING SETがそのSQLに適用されます。その結果、本来の論理的順序の規則が破られ、マッピング関係は論理的関係に最初に影響し、構文的関係は二番目に影響します。

混乱しましたか？そうですね。もっと表面的なことに戻りましょう。

何を学びましたか？

SELECT文は簡単そうに見えますが、SQL文の中で最も難しい部分でしょう。SELECT文はこれらの参照をまとめ、論理的なルールによってソーステーブルとターゲットテーブルをマッピングします。SELECT文はこれらの参照をまとめ、論理的なルールによってソーステーブルをターゲットテーブルにマップします。

SQL言語をしっかり学びたいのであれば、SELECT文を使った他のステートメントを理解する必要があります。 SELECTは構文構造の中では***キーワードですが、マスターすべきは***キーワードです。

10、SQL文をいくつかの簡単なキーワードで：DISTINCT、UNION、ORDER BY、OFFSET

セレクト・ユリサイドの複雑さを学んだ後は、もっとシンプルなものを見てみましょう：

• セット操作
• ソート操作

セット演算：

集合操作の主な操作はコレクションにあり、実際にはテーブルに対する操作を指します。概念的にはよく理解できます：

• DISTINCT マッピング後にデータの重複を削除します。
• UNION 2つのサブクエリをつなぎ合わせ、重み付けを解除します。
• UNION ALL は、重複排除なしで 2 つのサブクエリをつなぎ合わせます。
• EXCEPT は、2 番目の単語クエリの結果を *** サブクエリから削除します。
• INTERSECT 両方の副照会に含まれる結果を保持し、重複を取り除きます。

仕分け作業：

ソートは論理的な関係とは関係ありません。これはSQL固有の機能です。ソートはSQL文***の中だけでなく、SQL文***の実行中にも行われます。ORDER BYとOFFSET...FETCHを使用することは、データの順序を確実にする最も効率的な方法です。他の全てのソート方法は、再現可能なソート結果を与えますが、多少ランダムです。

(/.(//-/-/-/-/-セ/)。

仕事でSQLを思う存分使いましょう！

他の言語と同じように、SQLを学ぶには多くの練習が必要です。上記の10の簡単なステップは、あなたが毎日書くSQL文の理解を深めるのに役立ちます。一方、よくある間違いから多くの経験を得ることもできます。以下の2つの記事は、JAVAや他の開発者がよく犯すSQLの間違いについてです：

• Java開発者がSQLを書くときに犯しがちな間違い
• Java開発者がSQLを書くときに犯しがちなその他の間違い