SQL DISTINCT Clause

SQL: DISTINCT Clause

 The SQL DISTINCT clause allows you to remove duplicates from the result set. The SQL DISTINCT clause can only be used with SQL SELECT statements

SYNTAX:

SELECT DISTINCT columns
FROM tables
WHERE predicates;

For example:

Single field example

The simplest way to use the SQL DISTINCT clause would be to return a single field that removes the duplicates from the result set.

SELECT DISTINCT city
FROM suppliers;

This SQL DISTINCT clause example would return all unique cities from the suppliers table.

Multiple fields example

The SQL DISTINCT clause can be used with more than one field in your SQL SELECT statement.

SELECT DISTINCT city, state
FROM suppliers;

This SQL DISTINCT clause example would return each unique city and state combination. In this case, the DISTINCT applies to each field listed after the DISTINCT keyword.

SQL: WHERE Clause

The SQL WHERE clause allows you to filter the results from an SQL statement - SQL SELECT statement, SQL INSERT statement, SQL UPDATE statement, or SQL DELETE statement.

 It is difficult to explain the syntax for the SQL WHERE clause, so instead; we'll take a look at some examples.

Single condition example

SELECT * FROM suppliers
WHERE supplier_name = 'IBM';

In this SQL Where clause example, we've used the SQL WHERE clause to filter our results from the suppliers table. The SQL statement above would return all rows from the suppliers table where the supplier_name is IBM. Because the * is used in the select, all fields from the suppliers table would appear in the result set.

 Using SQL "AND" condition example

SELECT * FROM suppliers
WHERE supplier_city = 'Chicago' AND supplier_id > 1000;

This SQL Where clause example uses the WHERE clause to define multiple conditions. In this case, this SQL statement uses the SQL "AND" Condition to return all suppliers that are located in Chicago and whose supplier_id is greater than 1000.

Using SQL "OR" condition example

SELECT supplier_id  FROM suppliers
WHERE supplier_name = 'IBM' OR supplier_name = 'Apple';

This SQL Where clause example uses the WHERE clause to define multiple conditions, but instead of using the SQL "AND" Condition, it uses the SQL "OR" Condition. In this case, this SQL statement would return all supplier_id values where the supplier_name is IBM or Apple.

 Joining Tables example

SELECT suppliers.suppler_name, orders.order_id
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id AND suppliers.supplier_city = 'Atlantic City';

This SQL Where clause example uses the SQL WHERE clause to join multiple tables together in a single SQL statement. This SQL statement would return all supplier names and order_ids where there is a matching record in the suppliers and orders tables based on supplier_id, and where the supplier_city is Atlantic City.