Understanding SQL Precedence and Sorting in SELECT Statements
Explore the rules of precedence in SQL for evaluating expressions, overriding the default order using parentheses, and sorting rows in SELECT statements using ORDER BY clause with ASC and DESC options. Understand how to structure SELECT statements based on precedence rules for effective querying.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
The rules of precedence determine the order in which expressions are evaluated and calculated. The next table lists the default order of precedence. You can override the default order by using parenthesesaround the expressions you want to calculate first.
Order Evaluated Operator 1 Arithmetic operators 2 Concatenation operator 3 Comparison conditions 4 IS [NOT] NULL, LIKE, [NOT] IN 5 [NOT] BETWEEN 6 NOT logical condition 7 AND logical condition 8 OR logical condition
In the previews slide there are two conditions: The first condition is that the job ID is AD_PRES and the salary is greater than $15,000. The second condition is that the job ID is SA_REP. Based on the precedence rules, the SELECT statement reads as follows: Select the row if an employee is a president and earns more than $15,000, or if the employee is a sales representative.
In thepreviews example, there are two conditions: The first condition is that the job ID is AD_PRES or SA_REP. The second condition is that salary is greater than $15,000. Based on the precedence rules, the SELECT statement reads as follows: Select the rows if the employee is president or sales representative, and if the employee earn more than 15,000$
SQL allows sorting resulted rows by using the ORDER BY clause in: ASC: ascending order (the default order) .(see Example 10) DESC: descending order.(see Example11) The ORDER BY clause comes last in the SELECT statement
In term of syntax, generally, NOTcomes between experand comparison operator E.g Select fname, age Feom emp_table Where dept_num NOT IN(1,2);
This syntax is right for the operators (IN, Between.. And .., LIKE, IS NULl) BUT In case the symbolic comparison operator (>,<, >=,<=,=,<>) there will be an error E.g Select fname, age Feom emp_table Where dept_num NOT >3;
The Solution is to use NOT pefore the whole comparsion condition i.e. NOT(exper comparison operator) E.g Select fname, age Feom emp_table Where NOT (dept_num>3);
Assume that weve created the following table: Create table test2( col1 number(2), col2 number(2)); Then fill it with the values Col1 col2 1 9 2 8 3 10 2 10 3 5 4 4 3 4
The result of the query Select * From test2 Oreder by col2,col1; Col1 col2 Col1 col2 3 4 4 4 And it s NOT the same as ordering based on the last column (col2) which is: 4 4 3 4 3 5 3 5 2 8 2 8 1 9 1 9 2 10 3 10 3 10 2 10
