Knex.Js. Subqueries in Examples

Artem Diashkin
Jul 22, 2020 · 3 min read

What if we need to fetch users that have age more that average age of all users? Or we need to get some data and after that find another data that is depended from previous result? Let’s find out how to handle such scenarios only with one query using knex.js

For visual explanation of “what will happen if…” cases I will be using express.js for server-side and PostgreSQL database from previous article: Knex.Js. Beginner’s Guide in Examples

Old-fashion way

What if we need to fetch users that have an age more than the average age of all users?

First, let’s see what we’ve got in our tests database (users table):

As you probably know the simplest way is to implement it in an old-fashion way — fetch data in two steps:

  • Get average age (avg-age) of all users in the database;
SELECT AVG(age)
FROM users;
  • Get users that have age more than previously fetched result;
SELECT *
FROM users
WHERE users.age > <avg-age>

SQL Example:

Let's execute SQL query…

SELECT AVG(age)
FROM users;

… and see what we will get:

Knex.js Example:

SQL Example:

Let's execute SQL query…

SELECT *
FROM users u
WHERE u.age > 36.75;

… and see what we will get:

Knex.js Example:

Easy but not effective… Let’s see on the another, more elegant approach.

Subquery-fashion way

The simplest (I think) example of a subquery solution of a previous task will look like this:

SQL Example:

SELECT u.first_name,
last_name,
u.age
FROM users u
WHERE u.age >
(
SELECT AVG(age)
FROM users
);

As you can see, we just combined two SQL queries into one. Our subquery will be evaluated first (average age) and only after that outer query data will be fetched:

But how to do that using the knex.js library? Let’s find out

Knex.js Example:

Result will be exactly the same:

Simple, isn’t it? :)

LITSLINK

LITSLINK’s team is sharing their experience

By LITSLINK

It's a test newsletter! Take a look.

By signing up, you will create a Medium account if you don’t already have one. Review our Privacy Policy for more information about our privacy practices.

Check your inbox
Medium sent you an email at to complete your subscription.

Artem Diashkin

Written by

Java, Spring, Node.js, AdonisJs, React.js and Flutter developer

LITSLINK

LITSLINK

Hands-on up to date experience sharing of the most skilled & talented software developers right here, right now. Subscribe to learn & understand more about the Software World.

Artem Diashkin

Written by

Java, Spring, Node.js, AdonisJs, React.js and Flutter developer

LITSLINK

LITSLINK

Hands-on up to date experience sharing of the most skilled & talented software developers right here, right now. Subscribe to learn & understand more about the Software World.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store