Logic Behind SQL queries - part 1

Hello,

Solving SQL queries is somewhat difficult but it can be solved ease with by knowing some logical tricks and understanding the basics.

First, let us know about the control flow of the SQL statements.

Generally, the SQL statement is in the form of

select .... from ..... where .... group by .... having .... order by .... limit

First, the query should know from where the data should extract by "from" and how the columns should be extracted from "where" for the query. Then, the data is selected. Then, if any aggregation of columns then "group by". Then, if any ordering of data is required, we can get it by "order by". Then, we can limit the columns selected by "limit".

Trick 1: Understanding how joins work

In the above the schema depicts an application where its member (memid) can book a slot. 👇

Let consider a query,

select * from cd.bookings table1 join cd.members table2 on table1.memid=table2.memid;

In this query, cd.bookings is table1 and cd.members is table2.
We join them using table1.memid = table2.memid.

Think of it like this:

• Imagine a pointer p1 starting at the first row of table1 and a pointer p2 starting at the first row of table2.

• SQL compares p1.memid with p2.memid.

• If they match, that row from table1 is joined with the row from table2 and added to the result.

• Then p2 moves to the next row in table2 and the comparison repeats.

• When p2 reaches the end of table2, p1 moves to the next row in table1, and p2 starts again from the top of table2.

• This continues until all rows in table1 have been compared with table2.

This is a simple way to imagine how a join works.
In reality, databases may use faster methods (like indexes, hash joins, or merge joins), but logically it’s similar to comparing each row from table1 with each row from table2 until all matches are found.

Note:

1. This content is AI generated with help of AI.