Jump to content

Questions about the usage of DISTINCT and NOT syntax


Patty_Tu

Recommended Posts

Hi There,

I have two questions about SQL syntax, the first one is about "SELECT DISTINCT" and the other is "NOT".

Question 1:

For example, "Customers" table has fields including CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country. From my understanding, if my SQL statement is SELECT DISTINCT Country FROM Customers; then it will show the list of unique countries. However, I'd like to know what does it mean if my SQL statement is SELECT DISTINCT Country, City, CustomerName FROM Customers; ? Why I don't have to write SELECT DISTINCT Country, DISTINCT City, DISTINCT CustomerName?

You may refer to this session: https://www.w3schools.com/sql/sql_distinct.asp

Question 2:

I'm working on the session, and there's an example as below:

image.thumb.png.b5e57811cd940d448af107c6540848bf.png 

I understand that this example would like to exclude BOTH Country = "Germany" and Country = "USA", but I don't understand why using the operator [AND] instead of [OR]? Doesn't [AND] represent the intersection while [OR] mean union? So, if I want to exclude BOTH "Germany" and "USA", why I can't use [OR] as the operator in this example?

Link to comment
Share on other sites

The DISTINCT modifier doesn't apply to fields, it applies to the query as a whole. You can think of "SELECT DISTINCT" as a single keyword which searches for unique rows.

The AND operator will return a row only if both conditions are true. The OR operator will return a row if either one of the conditions is true.

The query is looking for rows where Country is not Germany AND also Country is not USA.

If you used OR instead then it would return all of the rows.

  • The expression (NOT Country='Germany') means "all possible values except Germany"
  • The expression (NOT Country='USA') means "all possible values except USA"
  • The union [OR] of those two sets is all possible values, because any value that one of the sets lacks exists in the other set.

 

  • Like 1
Link to comment
Share on other sites

On 10/16/2021 at 1:23 AM, Ingolme said:

The DISTINCT modifier doesn't apply to fields, it applies to the query as a whole. You can think of "SELECT DISTINCT" as a single keyword which searches for unique rows.

The AND operator will return a row only if both conditions are true. The OR operator will return a row if either one of the conditions is true.

The query is looking for rows where Country is not Germany AND also Country is not USA.

If you used OR instead then it would return all of the rows.

  • The expression (NOT Country='Germany') means "all possible values except Germany"
  • The expression (NOT Country='USA') means "all possible values except USA"
  • The union [OR] of those two sets is all possible values, because any value that one of the sets lacks exists in the other set.

 

Got it! Thank you so much for your response! It's really helpful to me!!

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...