What does distinct mean in sql

The first phase is called the lexical analyzer. The purpose of this phase it to see if the code is syntactically correct. The second phase is the parser for either a compiler or an interpreter. For instance, the C language compiles the a given program into assembly object code. It does not actual execute the program. The BASIC language which is an interpreter will execute the program line by line until a end of program is found. I did a lot of programming in these languages in the s. The final answer is the fact that Transact SQL is a domain specific language which is interpreted by the database engine.

With any language, the designer has to come up with a set of rules. Of course, the optimizer that creates the execution plan might actually change the order if necessary. The image below is taken from the MSDN webpage.

To recap, a solid understanding of the execution order of a SELECT statement is valuable when trying to optimize query plans. Today, we have seen both SQL statements execute in the same amount of time. There will be times in which two statements will return the same results but with widely different execution times. The simple PETS dataset was manually created by this author. However, there are animal shelters that might have similar data. They might want to know the average age of a given pet.

Does the following T-SQL generate the same results? The image below shows two different averages. Therefore, the results are not the same. We have two animals that are exactly 4 years old. This removal causes the two statements to come up with different results. What happens if we ask for distinct average by pet type? Since the duplicate age was between one cat record and one dog record, the duplicate records issue is removed.

There are no longer any duplicate cat or dog records. If we look at the actual query plan, we will see that same set of operators are used. In general, any type of sort is an expensive operation. Does the query plan different between these two statements? Not only are the actual execution plans different. But the first statement executes 4 times faster than the second statement.

Again, any type of sort is an expensive operation. The first statement just counts the number of records. Then, it counts the number of records while tossing any duplicate or NULL values. Let us now revisit the idea that the Adventure Works company sample database has too many job titles for the number of employees. The image below shows my remote desktop connect to this virtual machine. The first query a developer might try is to get a count of job titles as seen below.

However, this statement does not show the break down by employees by job title. Python Turtle. Verbal Ability. Interview Questions. Company Questions. Artificial Intelligence. Cloud Computing. Data Science. Angular 7. Machine Learning. Data Structures. Operating System. Computer Network. Compiler Design. Computer Organization. Discrete Mathematics. If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:.

Exercise: Select all the different values from the Country column in the Customers table. Report Error. Your message has been sent to W3Schools. W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning.