Jamie Balfour

Welcome to my personal website.

Find out more about me, my personal projects, reviews, courses and much more here.

Part 3.2Retrieving data from tables

Part 3.2Retrieving data from tables

This article will cover retrieval of information.

Selection

The SELECT keyword is used to obtain all relevant information based on a query.

For instance, running a SELECT query on a table with no conditions would look like:

SQL
SELECT * FROM `mydb`.`mytable`
        

This will select all (symbolised by the *, which is the Unix symbol for all) from the table called mytable which is found within the mydb database.

Note the dot between the database name and the table name is used for contatenation of a database and table, this simply means that the table exists within the database. This is crucial if the database has not already been selected as shown in the previous article.

The following table is an example table from some database:

ID First_name Second_name Country
0 Jack Adams US
1 Calum Costner UK
2 James Maclean UK
3 Link Williams UK

Running the previous query on the following table will return the whole table, and there will be no modifications made to the database.

Selection can also be made to be more specific, that is essentially selecting only what is needed and removing all of the rest. This can be done by providing columns to view.

The following query returns only the ID and First_name fields:

SQL
SELECT ID, First_name FROM `mydb`.`mytable`
        
ID First_name
0 Jack
1 Calum
2 James
3 Link

Selecting a database

Running SQL commands on a single database can be made more efficient if the database is selected first. This also shortens queries by removing the need to reference the database each time.

To select a database, the USE keyword is used. Assuming the database is called mydb, the following would select that database:

SQL
USE `mydb`
        

As a result, the SELECT query from above can be simplified to:

SQL
SELECT ID, First_name FROM `mytable`
        

Selecting unique data

SQL also provides a DISTINCT keyword that forces only single instances of a column in the results. For instance, say you wanted count all of the individual locations of people so you can figure out how many countries have participated in a survey, you could simply run the following:

SQL
SELECT DISTINCT Country FROM `mytable`
        

From the above table, this will return:

Country
US
UK

Binding tables to variables

As well as being able to simply select a table, tables can be named or bound to variables, so that fields can then be accessed through the table directly and to prevent confusion:

SQL
SELECT ID, First_name FROM 'mydb'.'countries' c
        

Null

SQL also includes a value known as a special null value. Null represents a value of nothing. It is not the same as an empty field, a 0 value or a false value. It represents nothing at all. Sometimes a selection will return null in some fields, meaning nothing has been returned.

Feedback 👍
Comments are sent via email to me.