SQL VISUAL QUICKSTART GUIDE- P23:SQL (pronounced es-kyoo-el) is the standard programming language for creating, updating, and retrieving information that is stored in databases. With SQL, you can turn your ordinary questions (“Where do our customers live?”) into statements that your database system can understand (SELECT DISTINCT city, state FROM customers;) | Chapter 7 Creating Joins with JOIN or WHERE Creating Joins with JOIN or WHERE You have two alternative ways of specifying a join by using JOIN syntax or WHERE syntax. SQL-92 and later standards prescribe JOIN syntax but older standards prescribe WHERE hence both JOIN and WHERE are used widely and are legal in most DBMSs. This section explains the general syntax for JOIN and WHERE joins that involve two tables. The actual syntax that you ll use in real queries will vary by the join type the number of columns joined the number of tables joined and the syntax requirements of your DBMS. The syntax diagrams and examples in the following sections show you how to create specific joins. To create a join by using JOIN Type SELECT columns FROM tablel join_type table2 ON join_conditions WHERE search_condition GROUP BY grouping_columns HAVING search_condition ORDER BY sort_columns columns is one or more comma-separated expressions or column names from tablel or table2. If tablel and table2 have a column name in common you must qualify all references to these columns throughout the query to prevent ambiguity see Qualifying Column Names earlier in this chapter. tablel and table2 are the names of the joined tables. You can alias the table names see Creating Table Aliases with AS earlier in this chapter. join_type specifies what kind of join is performed CROSS JOIN NATURAL JOIN INNER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN or FULL OUTER JOIN. join_conditions specifies one or more join conditions to be evaluated for each pair of joined rows. The ON clause isn t allowed in cross joins and natural joins. A join condition takes this form tablel. column op table2. column op usually is but can be any comparison operator or refer to Table in Chapter 4 . You can combine multiple join conditions with AND or OR see Combining and Negating Conditions with AND OR and NOT in Chapter 4. The WHERE and ORDER BY clauses are covered in Chapter 4 GROUP BY and HAVING are covered in Chapter 6. 200 .
