Selecting Specific Columns: Pulling Data by Column Names

When we pull data from a database, we rarely care about literally every column. For example, in the employees table, there might be 15 columns: first name, last name, birth date, job title, salary, hire date, and so on. But maybe you just want to know their names and job titles. Makes sense, right? Grabbing all the data is pointless and inefficient. That’s where the art of selecting specific columns comes in.

If you want an analogy, it’s like having a basket with oranges, apples, and bananas, but you only want to grab the apples. Pretty cool, right? That’s exactly what we’re gonna do!

Syntax

Just a reminder, SQL was designed to be super user-friendly.

First off, query text case doesn’t matter. You can write SELECT, Select, or select and it’ll all work. Second, line breaks don’t matter at all. The DBMS will turn your query into one long string anyway, so write it however you like.

As you probably guessed, SELECT and FROM aren’t the only keywords you’ll use. Otherwise, SQL wouldn’t be such a hot topic. The full-blown SQL query looks like this:

SELECT columns
FROM table
WHERE condition
GROUP BY columns
HAVING columns
ORDER BY sorting

Where:

• columns — the names of the columns you want to get.
• table — the name of the table you’re pulling data from.
• condition — the condition for filtering rows.
• sorting — the rows and the sort order.

Sounds easy? Let’s break it down with a real example. But let’s start with something simple.

Basic Query Example

Let’s say we have a students table with info about students. Here’s what the table structure might look like:

Now we want to get just the last_name and grade of all students.

The query will look like this:

SELECT last_name, grade
FROM students;

Result:

Congrats, you just saved your database some resources and made the result way more readable!

Concatenating Strings

You already know how to select data, so let’s try something cooler. In our table, first and last names are in different columns. Let’s write a query that gives us a column with the student’s full name.

To glue two strings together in PostgreSQL, you use the || operator. Here’s what our SELECT query will look like:

SELECT first_name || last_name, grade
FROM students;

Result:

Hmm. Looks like something’s missing. Like, maybe a space between the first and last name! Let’s fix that.

SELECT first_name || ' ' || last_name, grade
FROM students;

Result:

Nice. I like how the result table looks now, but what about the header? Wouldn’t it be better to see full name, or just name, instead of first_name || ' ' || last_name? That’s not pretty or practical. But there’s a fix for that too.

Selecting with Aliases

You can make SQL queries more readable by using aliases. It’s just a way to give a column a new name for the duration of the query. Aliases use the AS keyword (technically you can skip it, but for readability, don’t).

Check out this example:

SELECT first_name AS "First Name", last_name AS "Last Name", grade AS "Grade"
FROM students;

Result:

Here we:

1. Renamed the columns to make them more understandable in English.
2. Used aliases in the query with AS.

If your boss or client needs to see user data and you don’t want them to go gray reading the table — aliases are your friend.

Now let’s make our full name query a bit nicer.

SELECT first_name || ' ' || last_name  AS "Full Name", grade  AS "Grade"
FROM students;

Result:

Perfect. Just what we wanted.

Why Select Only Some Columns?

1. Performance

Imagine you’re working with a massive table with millions of rows and hundreds of columns. Pulling all the data with SELECT * could take minutes or even hours, and eat up a ton of server resources. This way, you only get what you actually need.

2. Readability

When you only select the columns you need, the result is way easier to understand. Otherwise, your result will look like trying to read a never-ending news feed on a Friday night.

3. Fewer Mistakes

The less data you process in your query, the less likely you are to mess something up. Especially if you’re going to work with that data later.

What Should You Keep in Mind?

Table Aliases

Another way to deal with long table names is to use table aliases. They should remind you of the original table name or be logically related to its contents.

SELECT sa05.first_name, sa05.course_id
FROM students_archive_2005 AS sa05

This method is especially handy if you have long table names like university_students_enrollments_records — you can use usr or us

Common Mistakes When Selecting Specific Columns

1. Typos in column names. If you type a column name wrong, you’ll get an error like: ERROR: column "lastname" does not exist. Double-check your column names.

2. Conflicting names. When your query uses multiple tables, always specify which table a field belongs to. For example, students.first_name.

3. Using SELECT * — this is a rookie trap. Sure, it works and it’s super convenient, but in big projects it’s bad practice! Always pick only the columns you really need.

2

Task

SQL SELF, level 2, lesson 0

Locked

Extracting Data Using Aliases

Extracting Data Using Aliases

Open

2

Task

SQL SELF, level 2, lesson 0

Locked

Creating a new column using data from other columns

Creating a new column using data from other columns

Open