Views 3. Table Links 3. Defining Table Aliases 3. The where Clause 3. The group by and having Clauses 3. The order by Clause 3. Ascending Versus Descending Sort Order 3. Sorting via Expressions 3. Sorting via Numeric Placeholders 3. Exercises 3. Filtering 4. Condition Evaluation 4.
Using Parentheses 4. Using the not Operator 4. Building a Condition 4. Condition Types 4. Equality Conditions 4. Inequality conditions 4. Data modification using equality conditions 4. Range Conditions 4. The between operator 4. String ranges 4. Membership Conditions 4. Using subqueries 4.
Using not in 4. Matching Conditions 4. Using wildcards 4. Using regular expressions 4. Exercises 4. Querying Multiple Tables 5. What Is a Join? Cartesian Product 5. Inner Joins 5. Joining Three or More Tables 5. Using Subqueries as Tables 5. Using the Same Table Twice 5. Self-Joins 5. Join Conditions Versus Filter Conditions 5. Exercises 5. Working with Sets 6. Set Theory Primer 6. Set Theory in Practice 6.
Set Operators 6. The union Operator 6. The intersect Operator 6. The except Operator 6. Set Operation Rules 6. Sorting Compound Query Results 6.
Set Operation Precedence 6. Exercises 6. Data Generation, Conversion, and Manipulation 7. Working with String Data 7. String Generation 7. Including single quotes 7. Including special characters 7.
String Manipulation 7. String functions that return numbers 7. String functions that return strings 7. Working with Numeric Data 7. Performing Arithmetic Functions 7. Controlling Number Precision 7.
Handling Signed Data 7. Working with Temporal Data 7. Dealing with Time Zones 7. Temporal Data Generation 7. String representations of temporal data 7. String-to-date conversions 7.
Functions for generating dates 7. Temporal Data Manipulation 7. Temporal functions that return dates 7. Temporal functions that return strings 7. Temporal functions that return numbers 7. Conversion Functions 7. Exercises 7. Grouping and Aggregates 8. Grouping Concepts 8. Aggregate Functions 8.
Implicit Versus Explicit Groups 8. Counting Distinct Values 8. Using Expressions 8. How Nulls Are Handled 8. Generating Groups 8. Single-Column Grouping 8. Multi-Column Grouping 8. Grouping via Expressions 8.
Generating Rollups 8. Group Filter Conditions 8. Exercises 8. Subqueries 9. What Is a Subquery? Subquery Types 9. Noncorrelated Subqueries 9. Multiple-Row, Single-Column Subqueries 9. The in operator 9. The all operator 9. The any operator 9. Multicolumn Subqueries 9. Correlated Subqueries 9. The Exists Operator 9. Data Manipulation Using Correlated Subqueries 9. When to Use Subqueries 9. Subqueries as Data Sources 9. Table fabrication 9. Task-oriented subqueries 9. Subqueries in Filter Conditions 9.
Subqueries as Expression Generators 9. Subquery Wrap-up 9. Exercises 9. Joins Revisited Outer Joins Left Versus Right Outer Joins Three-Way Outer Joins Self Outer Joins Cross Joins Natural Joins Exercises Conditional Logic In the first version you should actually add a comment indicating that. Columns in both tables must have the same value to satisfy the join condition. In the example provided, the query:. If you want to use columns in both tables that have the same name, but not all of them like the natural join you could execute a query like this:.
This way the tables are joined just using the key column to satisfy the condition, whereas the natural join would also take the value column into account. If the column names to be used in the join condition are different in both tables which is most common, I think then you would want to use the JOIN…ON syntax.
This way you tell the SQL engine exactly which conditions to use for the join. The names can be different in both tables. One thing that takes the most getting used to is the outer join. In Oracle SQL you are probably used to writing something like this:.
Take the table on the LEFT of this command and join its rows with rows in the table on the right if they exist. This syntax is unfortunately not supported by Oracle SQL. On two simple tables the union can easily be used, but imagine your real world query consisting of dozens of tables where just a couple should be outer joined both ways. Copy-Paste will come in handy here, but think about a change in the specification. Chances are you will forget to implement the change in one of the queries.
Your SQL will be vendor independent like you would ever want to move away from Oracle and your statements are more self-documenting, relieving you of the duty of documenting your statements. Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed. View all articles by Patrick Barel.
USING column [ , column ].
0コメント