Exploring Records in a Database Using SELECT Statements

When starting a new SQL project, one of the first steps is exploring the data you already have. Understanding the structure and contents of your tables helps you understand what’s available and imagine what your final results might look like.

Use the SELECT statement to read specific columns from a table, view, or table-valued function.

Basic SELECT syntax:

SELECT column1,
	   column2,
	   column3
FROM schema_name.table_name;

SELECT column1,
	   column2,
	   column3
FROM schema_name.table_name;

• SELECT the columns you want to see
• FROM the table (or view) where the data is stored

For example, let’s look at a paintings table and return a list of titles and artists.

Table: Gallery.Paintings

Query:

SELECT Title,
	   Artist
FROM Gallery.Paintings;

SELECT Title,
	   Artist
FROM Gallery.Paintings;

Result:

What If You Don’t Know Column Names?

When working with an unfamiliar table, you can display all columns using the asterisk * wildcard symbol:

SELECT *
FROM schema_name.table_name;

SELECT *
FROM schema_name.table_name;

📌 Important Note: This approach is useful for quick exploration. However, it’s considered poor practice to use SELECT * in production queries. Instead, return only the columns you need.

Why Select Specific Columns?

Selecting specific columns provides several benefits:

• Faster query performance
• Smaller, more manageable results
• Improved readability
• Greater control if the table structure changes

Reading from Large Tables

Some tables have only a few rows, while others can contain millions. Querying large tables can be slow and and resource intensive. Before running a SELECT statement, it’s often helpful to determine the table size.

One way to do this is running a row count query:

SELECT COUNT(1)
FROM schema_name.table_name;

SELECT COUNT(1)
FROM schema_name.table_name;

Limiting the Rows You Return

When you only need a sample of the data (such as the first 100 rows), limit your results. This makes queries faster and easier to review. The exact syntax depends on your database platform.

SQL Server (T-SQL):

SELECT TOP(100) *
FROM schema_name.table_name;

SELECT TOP(100) *
FROM schema_name.table_name;

Oracle (PL/SQL):

SELECT *
FROM schema_name.table_name
WHERE ROWNUM <= 100;

SELECT *
FROM schema_name.table_name
WHERE ROWNUM <= 100;

PostgreSQL / MySQL:

SELECT *
FROM schema_name.table_name
LIMIT 100;

SELECT *
FROM schema_name.table_name
LIMIT 100;

Key Takeaways

• Use SELECT to read records from tables, views, or table-valued functions.
• SELECT * is useful for exploration, but list specific columns in final queries.
• Check row counts to understand table size before retrieving data.
• Limit rows when working with large datasets to improve performance and readability.

Mastering the SELECT statement is the foundation of SQL. Once you’re comfortable with it, you’ll be ready to move on to filtering, sorting, and aggregating data.