Chapter 3: Access SQL - Basic Concepts and Terminology
Document Details
Uploaded by SincereAlgorithm
جامعة كفر الشيخ
Tags
Summary
This chapter provides an overview of Access SQL, defining basic concepts and syntax, including SELECT, FROM, WHERE, GROUP BY, HAVING, and UNION statements. It offers explanations of how to use these statements. It's suitable for beginners learning about SQL.
Full Transcript
When you want to retrieve data from a database, you can request the data using Structured Query Language or SQL. SQL is a computer language very similar to English, but these database programs understand. Every query you run uses SQL behind the scenes. Understanding how SQL works ca...
When you want to retrieve data from a database, you can request the data using Structured Query Language or SQL. SQL is a computer language very similar to English, but these database programs understand. Every query you run uses SQL behind the scenes. Understanding how SQL works can help you create better queries, and it can make it easier for you to understand how to fix a query that doesn't return the results you want. What is SQL? Basic SQL statements: SELECT, FROM, and WHERE Sort results: ORDER BY SUMMARY DATA USE: GROUP BY AND HAVING Combine query results: UNION اىَفبٕيٌ األعبعيخ ٍٗصطيسبرٖب ثْبء اىدَيخAccess SQL: SQL. ٗ يَنْل غيت اىجيبّبد ثبعزخذاً ىغةخ اسعةزماٍبد اىَْةَةخ،عْذٍب رشيذ اعزشداد اىجيبّبد ٍِ قبعذح ثيبّبد ٗىنةِ ثةشاٍح قبعةذح اىجيبّةبد ٕةزٓ رفٖةٌس يغةزخذً مةو،ٕي ىغخ مَجية٘رش ربةجٔ اىيغةخ الّدييضيةخ حىةك زةذ مجيةشSQL. في اىن٘اىيظSQL ٔاعزماً رقً٘ ثزبغيي ٗيَنةِ ُ يغةٖو عييةل فٖةٌ ميفيةخ ح ةاذ، عيك حّبةبء اعةزماٍبد فعةوSQL يَنِ ُ يغبعذك فٌٖ ميفيخ عَو.اعزماً س يشخع اىْزبئح اىزي رشيذٕب ؟SQLٕ٘ ٍب WHEREٗ FROM ٗ: SELECT األعبعيخSQL عجبساد : ORDER BYفشص اىْزبئح HAVINGٗ: GROUP BY اعزخذاً اىجيبّبد اىَيخصخ : UNIONًدٍح ّزبئح اسعزما SQL is a computer language for working with sets of facts and the relationships between them. Relational database programs, such as Microsoft Office Access, use SQL to work with the data. Unlike many computer languages, SQL is not difficult to read and understand, even for a beginner. Like many computer languages, SQL is an international standard that is recognized by hardware standards such as ISO and ANSI. ؟SQLٕ٘ ٍب ٍثو،ٕي ىغخ مَجي٘رش ىيمَو ٍع ٍدَ٘عبد اىسقبئق ٗاىماقبد ثيْٖبس رغزخذً ثشاٍح قبعذح اىجيبّبد اىمائقيخSQL س يصةمت قةشاءح،ىيمَو عيك اىجيبّبدس ثخاف اىمذيةذ ٍةِ ىغةبد اىنَجية٘رشSQL ،Microsoft Office Access ٕٔ٘ ٍميبس دٗىي يزٌ اىزمشف عييةSQL ، ززك ثبىْغجخ حىك اىَجزذئيِس ٍثو اىمذيذ ٍِ ىغبد اىنَجي٘رش،َٖٔٗفSQL ANSI.ٗ ISOٍِ خاه خٖضح اىَمبييش ٍثو You can use SQL to describe sets of data that can help you answer questions. When using SQL, the correct syntax must be used. Syntax is the set of rules by which the elements of a language are properly combined. SQL syntax is based on English syntax, and uses many of the same elements as Visual Basic for Applications (VBA) syntax. ً ى٘ ف ٍدَ٘عبد ٍِ اىجيبّبد اىزي يَنِ ُ رغبعذك في الخبثخ عيك األعئيخس عْذ اعزخذاSQL ًيَنْل اعزخذا يدت اعزخذاً ثْبء اىدَيخ اىصةسيرس ثْةبء اىدَيةخ ٕة٘ ٍدَ٘عةخ اىق٘اعةذ اىزةي يةزٌ ٍةِ خاىٖةب دٍةح عْب ةش،SQL ٗيغةزخذً اىمذيةذ ٍةِ اىمْب ةش ّفغةٖب، حىك ثْةبء خَيةخ اىيغةخ الّدييضيةخSQL اىيغخ ثبنو سيرس يغزْذ ثْبء خَيخ Visual Basic for Applications (VBA).اىزي يغزخذٍٖب ثْبء خَيخ For example, a simple SQL statement that retrieves a list of last names with contacts whose first name might look something like the following: SELECT Last_Name FROM Contacts WHERE First_Name = 'Mary'; Note: SQL is not only used to manipulate data, but also to create and change the design of database objects, such as tables. The part of SQL used to create and change database objects is called Data Definition Language (DDL). DDL is not covered by this topic. ُ٘ اىزي رغزشد قبئَخ ثأعَبء اىمبئيخ ٍع خٖبد اسرصبه اىزي ينةSQL قذ رزببثٔ اىمجبسح اىجغيطخ،عيك عجيو اىَثبه :اعَٖب األٗه ٍب ييي SELECT Last_Name FROM Contacts WHERE First_Name = 'Mary'; ،ٓ ٗىنةِ يعةب لّبةبء رصةَيٌ مبئْةبد قبعةذح اىجيبّةبد ٗرغييةش، ىَمبىدخ اىجيبّبد فقةػSQL ًس يزٌ اعزخذا: ٍازةخ ( سDDL). اىَغزخذً لّببء مبئْبد قبعذح اىجيبّبد ٗرغييشٕب ىغخ رمشيف اىجيبّةبدSQL ٍثو اىدذاٗهس يغَك خضء ً ساخةع اىَقبىةخ احّبةبء خةذاٗه ٗ فٖةبسط ٗ رمةذييٖب ثبعةزخذا، ىَضيذ ٍةِ اىَميٍ٘ةبدDDL. يغطي ٕزا اىَ٘ظ٘ع ".اعزماً رمشيف اىجيبّبد To describe a set of data using SQL, type the SELECT statement. The SELECT statement contains a complete description of a set of data that you want to get from a database. This includes: Tables containing data. How to relate data from different sources. What fields or calculations will produce the data. The criteria the data must meet to be included. Whether and how to sort the results. SELECTعجبساد عيةكSELECT رسزة٘ اىمجةبسحSELECT. امزةت اىمجةبسح، SQLًى٘ ةف ٍدَ٘عةخ ٍةِ اىجيبّةبد ثبعةزخذا :ٗ ف مبٍو ٍدَ٘عخ ٍِ اىجيبّبد اىزي رشيذ اىسص٘ه عييٖب ٍِ قبعذح ثيبّبدس يبَو رىل ٍب ييي.اىدذاٗه اىزي رسز٘ عيك اىجيبّبد .ميفيخ يخ اىجيبّبد ٍِ ٍصبدس ٍخزيفخ .ٍب ٕي اىسق٘ه ٗ اىمَييبد اىسغبثيخ اىزي عزْزح اىجيبّبد .اىَمبييش اىزي يدت ُ رطبثقٖب اىجيبّبد ىزعَيْٖب .ٍب حرا مْذ رشيذ فشص اىْزبئح ٗميفيخ فشصٕب SQL statements Like a clause, an SQL statement has statements. Each statement implements a function of SQL statements. Some statements are required in the SELECT clause. The following table lists the most common SQL statements. SQL The action it performs wanted statement SELECT Lists the fields that contain important data. Yes FROM Lists the tables containing the fields included in the SELECT Yes statement. WHERE Specifies the field criteria that must be met by each record to be No included in the results. ORDER This method determines how the results are sorted. No BY GROUP In a SQL statement containing aggregate functions, it lists the Only if such BY fields that are not summarized in the SELECT statement. fields exist HAVING In an SQL statement containing aggregate functions, you No specify the conditions that apply to the fields that are SQL The action it performs wanted statement summarized in the SELECT statement. SQLعجبساد ثمط اىمجبساد ٍطي٘ثةخ فةيSQL. ثٖب عجبسادس رقً٘ مو عجبسح ثزْفيز داىخ ىمجبسادSQL رنُ٘ خَيخ،ٍثو اىدَيخ. األمثش شي٘عبSQL يغشد اىدذٗه اىزبىي عجبسادSELECT. خَيخ SQLعجبسح ٓالخشاء اىز يْفّز ٍطي٘ة SELECT.يغشد اىسق٘ه اىزي رسز٘ عيك اىجيبّبد اىٖبٍخ ٌّم FROM SELECT.رغشد اىدذاٗه اىزي رسز٘ عيك اىسق٘ه اىَذسخخ في عجبسح ٌّم WHERE رسةةذد ٍمةةبييش اىسقةةو اىزةةي يدةةت ُ يةةزٌ رسقيقٖةةب ث٘اعةةطخ مةةو عةةدو ىزعةةَيْٖب فةةي س.اىْزبئح ORDER.رسذد ٕزٓ اىطشيقخ ميفيخ فشص اىْزبئح س BY GROUP ٌ رغةةشد اىسقةة٘ه اىزةةي ىةةٌ يةةز، اىزةةي رسزةة٘ عيةةك داسد ردَيميةةخSQL فةةي خَيةةخ فقػ في زبىخ ٗخ٘د ٍثو BY SELECT.ريخيصٖب في عجبسح ٕزٓ اىسق٘ه HAVING رسذد اىبشٗغ اىزي رْطجق عيك، اىزي رسز٘ عيك داسد ردَيميخSQL في اىمجبسح س SELECT.اىسق٘ه اىزي رٌ ريخيصٖب في اىمجبسح Each SQL statement consists of terms, similar to parts of speech. The following table lists the types of SQL terms. SQL term Similar part the definition Example of speech identifier Noun A name you use to identify a database clients. [Telephone object, such as a field name. number] operator verb or A keyword that represents an action or AS adverb modifies an action. Fixed Noun A value that does not change, such as a number or NULL. SQL term Similar part the definition Example of speech Expression Adjective A combination of identifiers, operators, >= Products. [unit constants, and functions that evaluate price] to a single value. SQLششٗغ SQL. ٍببثٖخ ألخضاء اىناًس يغشد اىدذٗه اىزبىي ّ٘اع ٍصطيسبد، ٍِ ٍصطيسبدSQL رزأىف مو عجبسح ٍصةةةةطير ِخةةةضء ٍَبمةةةو ٍةةة اىزمشيف ٍثبه SQL ًاىنا اىَمشف ٌاع. ٍثو اعٌ زقو،اعٌ رغزخذٍٔ ىزمشيف مبئِ قبعذح ثيبّبد ]اىمَاءس [سقٌ اىٖبرف عبٍو ربغيو ٗ اىفمةةةةةةةةو.ميَخ عبعيخ رَثو حخشاء ٗ رمذه حخشاء AS adverb مبثذ ٌاع NULL.ٗ ٌ ٍثو سق،قيَخ س رزغيش رمجيش اىصفخ ٌرشميجخ ٍِ اىَمشفبد ٗ ع٘اٍو اىزبغيو ٗاه م٘اثذ ٗٗظةبئف يةز =>اىَْزدةةبدس [عةةمش.رقييَٖب حىك قيَخ ٗازذح ]اى٘زذح The SQL statement takes the general form: SELECT field_1 FROM table_1 WHERE criterion_1 ; Notes: Access ignores line breaks in an SQL statement. However, consider using line per statement to help improve the readability of your SQL statements for yourself and others. Each SELECT statement ends with a semi-colon (;). A semicolon can appear at the end of the last sentence or on a line by itself at the end of an SQL statement. WHEREٗ FROM ٗ: SELECT األعبعيخSQL عجبساد :ً اىَْ٘رج اىمبSQL رأخز اىمجبسح SELECT field_1 FROM table_1 WHERE criterion_1 ; : ٍازةبد فنةش فةي اعةزخذاً عةطش ىنةو عجةبسح ىيَغةبعذح عيةك، ٍٗةع رىةلSQL. ُ ف٘ا ةو اىغةطش فةي ثيةبAccess يزدبٕةو .ِ ىْفغل ٗىآلخشيSQL رسغيِ قبثييخ قشاءح عجبساد ٗ ثبجٔ ّقطزيِ (;)س يَنِ ُ رةٖش اىفب يخ اىبجٔ اىْقطيخ في ّٖبيخ اىدَيخ األخيشحSELECT رْزٖي مو اىمجبسح SQL.عيك عطش ثسذ رارٔ في ّٖبيخ خَيخ Example in Access The following shows what an SQL statement for a simple select query might look like in Access: 1. SELECT statement . FROM. Phrase 3. The phrase WHERE This example reads in the SQL statement "Select the data stored in the fields named 'Email address' and 'Company' from the table named 'Contacts', specifically those records in which the value of the field is 'City of Seattle'." Let's look at the example, one statement at a time, to see how SQL syntax works. Accessٍثبه في Access: سعزماً رسذيذ ثغيػ فيSQL ي٘ظر ٍب ييي اىبنو اىز قذ رجذٗ ثٔ اىمجبسح SELECTعجبسح1. FROM عجبسح. WHEREعجبسح3. رسذيةةذ اىجيبّةةبد اىَخضّةةخ فةةي اىسقةة٘ه اىَغةةَبح اعْةة٘اُ اىجشيةةذ الىنزشّٗةةياSQL "يقةةش ٕةةزا اىَثةةبه فةةي اىمجةةبسح ٗرسذيةذا ريةل اىغةداد اىزةي رنةُ٘ فيٖةب قيَةخ اىسقةو اٍذيْةخ،ٗااىبشمخا ٍِ اىدذٗه اىَغَك اخٖةبد اسرصةبها ".عيبرو SQL. ىَمشفخ ميفيخ عَو ثْبء خَيخ، عجبسح ٗازذح في مو ٍشح،دعْب ّطيع عيك اىَثبه SELECT [E-mail Address], Company This is the SELECT statement. It consists of an operator (SELECT) followed by two identifiers ([email address] and company). If the identifier contains spaces or special characters (eg "e-mail address"), it must be enclosed in square brackets. The SELECT statement does not have to indicate which tables contain the fields, nor can it specify which conditions must be met by the data that must be included. The SELECT statement always appears in front of the FROM clause in the SELECT clause. SELECTعجبسح SELECT [E-mail Address], Company ( ٍزج٘ع ثَمشفيِ ([عْة٘اُ اىجشيةذ الىنزشّٗةيSELECT) ٗيزنُ٘ ٍِ عبٍو ربغيوSELECT. ٕزٓ ٕي عجبسح ).ٗاىبشمخ فيدةت حزبغزةٔ ثةأق٘اط،)حرا مبُ اىَمشف يسز٘ عيك ٍغبفبد ٗ زشف خب خ (ٍثو اعْة٘اُ اىجشيةذ الىنزشّٗةيا.ٍشثمخ ٌشةشٗغ يدةت ُ يةز ٗس يَنْٖب رسذيةذ، اىدذاٗه اىزي رسز٘ عيك اىسق٘هSELECT س يدت ُ ر٘ظر عجبسح.رسقيقٖب ث٘اعطخ اىجيبّبد اىزي يدت رعَيْٖب SELECT. في خَيخFROM دائَب ٍبً عجبسحSELECT رةٖش عجبسح FROM Contacts This is the FROM clause. It consists of an operator (FROM) followed by an identifier (contacts). The FROM clause does not list the fields that you want to select. FROMعجبسح FROM Contacts ).( ٍزج٘ع ثَمشف (خٖبد اسرصبهFROM) ٗيزنُ٘ ٍِ عبٍو ربغيوFROM. ٕزٓ ٕي عجبسح. عشد اىسق٘ه اىزي رشيذ رسذيذٕبFROM س رقً٘ عجبسح WHERE City="Seattle" THIS IS THE WHERE Phrase. It consists of an operator (WHERE) followed by an expression (City="Seattle"). Note: Unlike the SELECT and FROM clause, the WHERE clause is not a required element of the SELECT clause. You can perform many of the actions that SQL enables you to do using SELECT, FROM, and WHERE statements. WHEREعجبسح WHERE City="Seattle" (City="Seattle").( ٍزج٘ع مزمجيشWHERE) ٗيزنُ٘ ٍِ عبٍو ربغيوWHERE. ٕزٓ ٕي عجبسح عْصةةشا ٍطي٘ثةةب فةةي خَيةةخWHERE س رنةةُ٘ عجةةبسح، FROMٗ SELECT ثخةةاف عجةةبسح: ٍازةةةخ SELECT. ٗ FROM ٗ SELECT ٍةِ اىقيةبً ثٖةب ثبعةزخذاً عجةبسادSQL يَنْل رْفيز اىمذيذ ٍِ الخشاءاد اىزي رَنْل WHERE. Like Microsoft Excel, Access lets you sort query results in a datasheet. You can also specify how in the query you want the results to be sorted when the query is run, by using the ORDER BY clause. If you are using an ORDER BY clause, it is the last statement in the SQL statement. The ORDER BY clause contains the list of fields that you want to use for sorting, in the same order that you want to apply the sorts. For example, suppose you want to sort the results first by the value of the Company field in descending order, and if there are records with the same value for Company, they are sorted along with the values in the Email Address in the field in ascending order. An ORDER BY clause might look like this: ORDER BY Company DESC, [E-mail Address] Note: By default, Access sorts values in ascending order (A through Z, smallest to largest). Use the DESC keyword to sort the values in descending order instead. : ORDER BYفشص اىْزبئح فةشص ّزةبئح اسعةزماً فةي ٗسقةخ ثيبّةبدس يَنْةل يعةب رسذيةذ اىطشيقةخAccess يزةير ىةل، Microsoft Excelٍثو ً حرا مْةذ رغةزخذORDER BY. ثبعةزخذاً عجةبسح،ًاىزي رشيذ ثٖب في اسعزماً فشص اىْزبئح عْذ ربةغيو اسعةزما SQL. فٖي عجبسح خيشح في خَيخ، ORDER BYعجبسح ثبىزشريةت ّفغةٔ اىةز رشيةذ رطجيةق، عيك قبئَخ اىسقة٘ه اىزةي رشيةذ اعةزخذاٍٖب ىيفةشصORDER BY رسز٘ عجبسح.ٔعَييبد اىفشص عيي ٗحرا مبّةذ ْٕةبك، ىْفزشض ّل رشيذ فشص اىْزبئح ٗس زغت قيَخ اىسقو ااىبشمخا ثزشريت رْبصىي،عيك عجيو اىَثبه يةةزٌ فشصٕةةب حىةةك خبّةةت اىقةةيٌ فةةي اعْةة٘اُ اىجشيةةذ الىنزشّٗةةيا فةةي اىسقةةو ثزشريةةت،عةةداد ثةةْفظ اىقيَةةخ ىيبةةشمخ : ٍب يييORDER BY رصبعذ س قذ ربجٔ عجبسح ORDER BY Company DESC, [E-mail Address] ً ٍِ األ ةغش حىةك األمجةش)س اعةزخذ، اىقيٌ ثبىزشريت اىزصبعذ (ٍِ حىكAccess يفشص،ثبنو افزشاظي: ٍازةخ. ىفشص اىقيٌ ثزشريت رْبصىي ثذس ٍِ رىلDESC اىنيَخ األعبعيخ Sometimes you may want to use summary data, such as total sales in a month, or the most expensive items in inventory. To do this, you must implement a field aggregate function in the SELECT statement. For example, if you want the query to show the number of email addresses listed for each company, your SELECT statement might look like this: SELECT COUNT([E-mail Address]), Company The aggregate functions that you can use depend on the type of data in the field or expression that you want to use. HAVINGٗ: GROUP BY اعزخذاً اىجيبّبد اىَيخصخ ٗ مثش اىمْب ةش رنيفةخ فةي، ٍثو حخَبىي اىَجيمبد في شٖش،قذ رشغت في ثمط األزيبُ في اعزخذاً ثيبّبد ٍيخصخ ُ حرا مْذ رشيذ، عيك عجيو اىَثبهSELECT. يدت رطجيق ٗظيفخ ردَيميخ زقو في عجبسح،اىَخضُٗس ىيقيبً ثزىل : ٍب يييSELECT فقذ ربجٔ عجبسح،يةٖش اسعزماً عذد عْبٗيِ اىجشيذ الىنزشّٗي اىَذسخخ ىنو ششمخ SELECT COUNT([E-mail Address]), Company When using aggregate functions, you should usually create a GROUP BY statement. The GROUP BY clause lists all fields for which an aggregate function is not applied. If you apply aggregate functions to all fields in a query, you do not need to create a GROUP BY clause. The GROUP BY clause immediately follows the WHERE clause or the FROM clause if there is no WHERE clause. The GROUP BY statement lists the fields as they appear in the SELECT statement. For example, continuing with the previous example, if the SELECT statement applies an aggregate function to [email address] and not to the company, then the GROUP BY clause would represent the following: GROUP BY Company GROUP BY عجبسح:رسذيذ اىسق٘ه غيش اىَغزخذٍخ في داىخ ردَيميخ GROUP BY رغةشد عجةبسحGROUP BY. يدةت عييةل عةبدح حّبةبء عجةبسح،عْذ اعةزخذاً اىةذاسد اىزدَيميةخ ِ ىة،ًمبفخ اىسق٘ه اىزي س رطجق داىةخ ردَيميةخ عييٖةبس حرا قَةذ ثزطجيةق داسد ردَيميةخ عيةك مةو اىسقة٘ه فةي اعةزما GROUP BY.رسزبج حىك حّببء عجبسح WHERE. حرا ىٌ ينةِ ْٕةبك عجةبسحFROM ٗ عجبسحWHERE ٍجبششح عجبسحGROUP BY رزجع عجبسح SELECT. اىسق٘ه مَب رةٖش في عجبسحGROUP BY رغشد عجبسح If you want to use criteria to limit results, but the field you want to apply criteria to is used in an aggregate function, you cannot use the WHERE clause. Alternatively, you can use the HAVING statement. A HAVING clause works like a WHERE clause, but is used for aggregated data. For example, suppose you use the AVG function (which calculates the average value) with the first field in a SELECT statement: SELECT COUNT([E-mail Address]), Company If you want the query to constrain results based on this COUNT value, you cannot use criteria for that field in the WHERE clause. Instead, put the criteria in the HAVING statement. For example, if you want the query to return rows only if there is more than one email address associated with the company, the HAVING statement might look like this: HAVING COUNT([E-mail Address])>1 Note: A query can include a WHERE clause and HAVING clauses — the criteria for fields that are not used in an aggregate function, and go in the WHERE clause, and criteria for fields that are used with aggregate functions, are used by the query in the HAVING clause. HAVING عجبسح:رقييذ اىقيٌ اىزدَيميخ ثبعزخذاً ٍمبييش اىَدَ٘عخ ٗىنِ اىسقو اىز رشيةذ رطجيةق اىَمةبييش عييةٔ يةزٌ اعةزخذأٍ فةي داىةخ،حرا مْذ رشيذ اعزخذاً ٍمبييش ىيسذ ٍِ اىْزبئح رمَةو عجةبسحHAVING. يَنْةل اعةزخذاً عجةبسح، ثذس ٍِ رىةلWHERE. فا يَنْل اعزخذاً عجبسح،ردَيميخ. ٗىنِ يزٌ اعزخذاٍٖب ىيجيبّبد اىَدَمخ، WHEREٍثو عجبسحHAVING اىزةي رسغةت ٍز٘عةػ اىقيَةخ) ٍةع اىسقةو األٗه فةي عجةبسحAVG ( افزةشض ّةل رغةزخذً اىذاىةخ،عيةك عةجيو اىَثةبه SELECT: SELECT COUNT([E-mail Address]), Company فةا يَنْةل اعةزخذاً ٍمةبييش،ٓ ٕةزCOUNT حرا مْذ رشيذ ُ يقً٘ اسعزماً ثزقييذ اىْزبئح اعزْبدا حىةك قيَةخ اىذاىةخ حرا مْةذ، عيةك عةجيو اىَثةبهHAVING. ظع اىَمبييش في عجبسح، ثذس ٍِ رىلWHERE. ىزىل اىسقو في عجبسح فقةذ،رشيذ ُ يقً٘ اسعزماً ثإسخبع ف٘ف فقػ حرا مبُ ْٕبك مثش ٍِ عْ٘اُ ثشيذ حىنزشّٗي ٗازذ ٍقزشُ ثبىبةشمخ : ٍب يييHAVING ربجٔ عجبسح HAVING COUNT([E-mail Address])>1 ٕٗةي ٍمةبييش اىسقة٘ه غيةشHAVING — ٗعجةبسادWHERE يَنةِ ُ يزعةَِ اسعةزماً عجةبسح: ٍازةةخ ٍةب ٍمةبييش اىسقة٘ه اىزةي يةزٌ اعةزخذاٍٖب ٍةع اىةذاسد، WHERE ٗرزٕت في عجةبسح،اىَغزخذٍخ في داىخ ردَيميخ HAVING. فيغزخذٍٖب اسعزماً في عجبسح،اىزدَيميخ When you want to review all the data returned by several identical select queries together, how many combined combinations, you can use the UNION operator. The UNION operator allows you to combine SELECT statements into one. SELECT statements that you combine must have the same number of output fields, in the same order, and with the same or compatible data types. When you run the query, the data from each set of corresponding fields is combined into a single output field, so that the query output contains the same number of fields for each select statement. Note: For union query purposes, the Number and Text data types are compatible. When using the UNION operator, you can also specify whether query results should contain duplicate rows, if any, by using the ALL keyword. The basic SQL syntax for a union query that combines SELECT statements is as follows: SELECT field_1 FROM table_1 UNION [ALL] SELECT field_a FROM table_a ; For example, let's say you have one table called "Products" and another one called "Services". The two tables contain fields that contain the name of the product or service, price, warranty or guarantee of availability, and whether you provide the product or service exclusively. Although the Products table stores warranty information, and the Services table stores warranty information, the basic information is the same (whether a particular product or service includes a quality promise). You can use a union query, like the following, to combine the four fields from the two tables: SELECT name, price, warranty_available, exclusive_offer FROM Products UNION ALL SELECT name, price, guarantee_available, exclusive_offer FROM Services ; : UNIONًدٍح ّزبئح اسعزما ، مةٌ ٍدَ٘عةخ ٍذٍدةخ،عْذٍب رشيذ ٍشاخمخ مو اىجيبّبد اىزي يزٌ حسخبعٖب ث٘اعطخ عذح اعزماٍبد رسذيذ ٍَبميخ ٍمب UNION.يَنْل اعزخذاً عبٍو ربغيو اىزةيSELECT فةي ٗازةذحس يدةت ُ ينةُ٘ ىمجةبسادSELECT دٍةح عجةبسادUNION يزير ىل عبٍو ربغيو ٍٗةع ّة٘اع اىجيبّةبد ّفغةٖب ٗ ّة٘اع اىجيبّةبد اىَز٘افقةخس،ٔ ثبىزشريةت ّفغة،ٔرقً٘ ثذٍدٖب عذد زقة٘ه الخةشاج ّفغة ثسية، يزٌ دٍح اىجيبّبد ٍِ مو ٍدَ٘عخ ٍِ اىسق٘ه اىَْبظشح في زقو حخةشاج ٗازةذ،ًعْذٍب رقً٘ ثزبغيو اسعزما.يسز٘ حخشاج اسعزماً عيك عذد اىسق٘ه ّفغٔ ىنو ٍِ عجبساد رسذيذ. ّ٘اع اىجيبّبد اسقٌا ٗاّصا ٍز٘افقخ، ألغشاض اسعزماً اىز٘زيذ: ٍازةخ ةةف٘فب ً يَنْةةل يعةةب رسذيةةذ ٍةةب حرا مةةبُ يدةةت ُ رزعةةَِ ّزةةبئح اسعةةزما، UNIONعْةةذ اعةةزخذاً عبٍةةو ربةةغيو ALL. ثبعزخذاً اىنيَخ األعبعيخ، حُ ٗخذد،ٍنشسح : مَب يييSELECT األعبعي سعزماً ارسبد يدَع ثيِ عجبسادSQL ينُ٘ ثْبء خَيخ SELECT field_1 FROM table_1 UNION [ALL] SELECT field_a FROM table_a ; عيك عجيو اىَثبه ،ىْفزشض ُ ىةذيل خةذٗس يغةَك ااىَْزدةبدا ٗيخةش يغةَك ااىخةذٍبداس يسزة٘ اىدةذٗسُ عيةك اىسق٘ه اىزي رسز٘ عيك اعٌ اىَْزح ٗ اىخذٍخ ٗاىغمش ٗ اىعةَبُ ٗ ظةَبُ اىزة٘فشٍٗ ،ةب حرا مْةذ رقةذً اىَْةزح ٗ اىخذٍخ ثبةنو زصةش س عيةك اىةشغٌ ٍةِ ُ خةذٗه اىَْزدةبد يخةضُ ٍميٍ٘ةبد اىعةَبُٗ ،يخةضُ خةذٗه ااىخةذٍبدا ٍميٍ٘بد ظَبّيخ ،فإُ اىَميٍ٘بد األعبعيخ ٕي ّفغٖب (عة٘اء مةبُ ٍْةزح ٗ خذٍةخ ٍميْةخ رزعةَِ ٗعةذا ثةبىد٘دح)س يَنْل اعزخذاً اعزماً ارسبدٍ ،ثو ٍب ييي ،ىذٍح اىسق٘ه األسثمخ ٍِ اىدذٗىيِ: SELECT name, price, warranty_available, exclusive_offer FROM Products UNION ALL SELECT name, price, guarantee_available, exclusive_offer FROM Services ;
