How to Select from One Table in SQL

The SELECT statement is used to select data from a database. The data returned is stored in a result table. Because the SELECT statement inquires about the contents of a table, it is called a query.

images/articles/mysql/select-from-one-table-in-sql.jpg 
SELECT column1, column2 FROM table_name;

1. All Fields

In its simplest form, a SELECT statement returns all the data in all the rows and columns in whatever table you specify. If you want to select all the fields available in the table,

SELECT * FROM table_name;

The asterisk (*) is a wildcard character that means everything. In this context, it means return data from all the columns.

2. Distinct Values

The SELECT DISTINCT statement is used to return only distinct or different values. Inside a table, a column often contains many duplicate values and sometimes you only want to list the distinct values.

SELECT DISTINCT column1, column2 FROM table_name;

Filter Based on Condition

The WHERE clause is used to filter records. It is used to extract only those records that meets a specified condition.

SELECT column1, column2 FROM table_name WHERE condition;

The following operators can be used in the WHERE clause:

  1. =: Equal
  2. <>: Not equal
  3. !=: Not Equal
  4. >: Greater than
  5. <: Less than
  6. >=: Greater than or equal
  7. <=: Less than or equal
  8. BETWEEN: Between an inclusive range
  9. LIKE: Search for a pattern
  10. IN: To specify multiple possible values for a column

Multiple Conditions

The WHERE clause can be combined with AND, OR, and NOT operators. The AND and OR operators are used to filter records based on more than one condition.

The AND operator displays a record if all the conditions separated by AND is TRUE. 

SELECT column1, column2 FROM table_name WHERE condition1 AND condition2;

The OR operator displays a record if any of the conditions separated by OR is TRUE. 

SELECT column1, column2 FROM table_name WHERE condition1 OR condition2;

The NOT operator displays a record if the condition is NOT TRUE.

SELECT column1, column2 FROM table_name WHERE NOT condition;

You can also combine the AND, OR and NOT operators.

Sorting Ascending or Descending

The ORDER BY keyword is used to sort the result-set in ascending or descending order. It sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

SELECT column1, column2 FROM table_name ORDER BY column1, column2 DESC;

Limit Number of Records

The LIMIT clause is used to specify the number of records to return. It is useful on large tables with thousands of records. Returning a large number of records can impact performance.

SELECT column_names FROM table_name WHERE condition ORDER BY column1 LIMIT number;