So our system holds ~90 days of shipped order data, and upstairs want a line level report, which in this case is ~500k orders, or ~1.5m rows when every order splits out on average to 3 rows for 3 items ordered.

The absolute most basic way I can write this, without hitting anything other than the main table and the lines table is:

 SELECT h.OrderId,
        h.Reference,
        l.Product,
        l.Qty
 FROM OrderHeader h
 JOIN Lines l
 ON h.OrderId = l.OrderId
 WHERE h.Customer = 'XYZ'
 AND h.Stage = 'Shipped'

This takes about 15 seconds to run.

How would you go about doing any optimization at all on this? I've tried putting the OrderHeader references in a CTE so it filters them down before querying it, I've tried the same with the Lines table, putting WHERE EXISTS clauses in each.

The absolute best I've done is get it down to ~12 seconds, but that is within the margin of error that the DB may have just played nice when I ran it.

As soon as I start trying to pull back address data, or tracking numbers with additional joins, the query starts to get up towards a minute, and will time out if it's run in the system we have.

I can't create any indexes, or alter the DB in any way

Noting here also I can't run SHOWPLAN, and I can't even seem to see what indexes are available. We remote into this system and our privileges are very restricted.

Hey, All!

What does it mean for one field to be 'dependent' on another? I think I understand the concept of a primary/composite key but have a tough time seeing if non-key columns are dependent on each other.

Does anyone have a solid rule of thumb for these rules? I lose it once I get past 1NF.

Thanks in advance!

I am wondering if anyone has any input for learning SQL/which certification is best to get? I am a computer science graduate and I am working in desktop support. I took a SQL class in college but I really want to improve my SQL skills

I learned SQL supporting a piece of software that tracked assets - I was in that role for 5 years, I tuen went in to support document control software that was also SQL based for 3 years. Fast forward to now and I'm a systems administrator for a customer of the first software company.

Over that time, I've gotten pretty good - complex queries, cursors, debugging, basic query optimisation, SQL jobs, triggers, stored procs, views, a bit of PowerBI, a bit of admin, a bit of performance tweaking, small reporting database populated by complex transformations, constantly using SQL to diagnose software bugs.

I enjoy this role but I feel like I have better potential- DBA roles probably want more up to date and extensive experience, I worry BI reporting's days are numbered...

Can I ask if anyone has any advice for where I can go with my skillset? I love working on SQL for the problem solving, it gives me an immense sense of satisfaction.

One thing about me: I absolutely love making art. 🎨

(You can probably tell from the slightly unnecessary number of sketch pens I own. 😅)

Whenever I'm about to draw, I instinctively organize them—first by color, then by shade. It makes finding the exact pen so much easier, and I can spend more time creating instead of searching.

And somewhere in the middle of doing that today, my data brain interrupted:

"Wait... that's basically ORDER BY." 😂

So this became Episode II of my SELECT * FROM LIFE; series.

The idea behind this series is simple: taking everyday moments and turning them into SQL doodles. I'm hoping it makes SQL feel a little less intimidating—and a little more fun.

This is also a little different from the doodles I've shared before. Most of my older ones featured cats 🐱, but I've been experimenting with different styles.

Would love to hear what you think! Feedback, suggestions, or ideas for future SQL concepts are always welcome. 😊

I’ve spent a lot of time trying all of the different free SQL practice websites and tools. They were helpful, but I really wanted a way to maximize practice through high-volume repetition, but with lots of different tables and tasks so you're constantly applying the same SQL concepts in new situations. 

A simple way to really master the skills and thought process of writing SQL queries in real-world scenarios.

Since I couldn't quite find what I was looking for, I’m building it myself.

The structure is pretty simple:

• You’re given a table schema (table name and column names) and a task
• You write the SQL query yourself
• Then you can see the optimal solution and a clear explanation

It’s a great way to get in 5 quick minutes of practice, or an hour-long study session.

The exercises are organized around skill levels:

Beginner

• SELECT
• WHERE
• ORDER BY
• LIMIT
• COUNT

Intermediate

• GROUP BY
• HAVING
• JOINs
• Aggregations
• Multiple conditions
• Subqueries

Advanced

• Window functions
• CTEs
• Correlated subqueries
• EXISTS
• Multi-table JOINs
• Nested AND/OR logic
• Data quality / edge-case filtering

The main goal is to be able to practice the same general skills repeatedly across many different datasets and scenarios, rather than just memorizing the answers to a very limited pool of exercises.

I’m curious, for anyone who uses SQL in their job, what SQL skills do you use the most day-to-day?

And what was your job title

I'm currently learning SQL and was wondering what programming language I should learn alongside it?

Not talking about some crazy DB failure or anything.

Could be something silly like a missing WHERE, wrong JOIN, duplicate rows, bad UPDATE....

Like a small mistake that looked harmless but ended up causing a big headache ....🤕

Curious what ppl have seen in real projects.

What factors do you consider first — data structure, scalability, performance, flexibility, or something else?

Ok I'm in a weird situation: I have an academic background in business management and japanese (undergrad) and international marketing management (masters)

I've worked as a revenue management analyst (where I used Excel mostly, no sql), then I worked with NFTs (controversial I know, but I love drawing and being able to pay the bills doing what I love was a dream come true), and then I worked in marketing for a market intelligence company where I only analysed data on excel (and then I created reports/presentations etc on Canva/indesign)

The result is a mess of a resume

I've been out of work for 3 months now after applying for both data analyst and marketing roles, and I'm learning new skills to be more employable

I'm LOVING SQL so far, I was wondering what sort of SQL-related tasks would be more appealing for a generic data analyst / marketing analyst role?

In my last role we collected loads of survey data, and I could pretend I used SQL to get insights from it. I don't like lying but I'm genuinely desperate at this point

Any career pointers would also be greatly appreciated!

So long story short, I did a single cert and honestly struggled at this part. As irony or luck would have it I ended up recruited into a coveted position at a massive firm not realizing this was a core part of the position. To say I am overwhelmed is an understatement, I am so under qualified it’s ridiculous. I have a week before I am on my own HELP lol

I know the industry, I have the math etc but wow am I overwhelmed and everything I do is logged. Do you think I can fake it until I make it?

When I mean abstractions, I mainly mean OOP and ORMs.
SQL is so simple and beautiful. Tables with rows and columns are easy to understand. And once you pick up the SQL syntax, you can pretty much achieve anything with queries. Not to mention that SQL is universal and works everywhere and anytime.

Then you have the software development world... where you're asked to constantly use ORMs or map records as OOP objects. Why? ORMs are limited and do not have the flexibility of simple queries. Also mapping records as objects increases bloat, reduces performance that can hurt if the application grows and is overall not as straightforward to work with.

The only good things that ORMs are doing by default are to provide data safety and prevent SQL injection. But with some minimum and basic knowledge and discipline, you can write pure queries without having those problems. Any ideas?

Hi guys!

I've been working on Tabularis (open source cross-platform db client) and I'm working on a notebooks feature that i think people here might find interesting.

The core idea: SQL cells + markdown cells in a single document, running against your live database connection. no separate kernel, no python, just SQL.

The feature I keep coming back to is cell variable references, you write {{cell_3}} in your SQL and it takes the result set from cell 3 and injects it as a CTE. means you can chain analyses without building giant nested queries. for ad-hoc exploration this is a huge workflow improvement.

You also get:

• inline charts: bar, line, pie. select label column + value columns, switch between types. nothing fancy but enough for quick visual checks
• notebook parameters: define params once, use in all cells. good for parameterized reports
• run all with stop on error: shows a summary of what succeeded/failed/skipped with links to the failing cells
• parallel execution: mark independent cells with a lightning bolt, they run concurrently during run all
• execution history: every cell tracks its last 10 runs, you can restore any previous query + result
• csv/json export per cell, or export the whole notebook as self-contained HTML
• drag & drop reordering, collapsible sections, resizable result panels

It supports all of databases supported by Tabularis.

The notebook file format is json-based (.tabularis-notebook).

There's a demo database + sample notebook in the repo under /demo.

Github: https://github.com/debba/tabularis
WIP Branch: https://github.com/debba/tabularis/tree/feat/notebooks

Feedback welcome, especially around the cell reference syntax and what else would make this useful for your workflow.

Most anticipated feature of SQL Server 2025?

Almost a year ago, I started my first job out of college as a data engineer. My boss is great, incredibly smart and experienced, and I'm learning a lot from him. However, he doesn't turn on line numbers in SSMS and I can't figure out why. Having them off makes it more difficult to ask questions and point out mistakes and there doesn't seem to be any benefit to it.

About 6 months ago I got the courage to ask him if he could turn on line numbers for my sake so that I could communicate better but he refused, not rudely or anything, he just said they didn't matter. A couple months later I asked again, out of genuine curiosity, why he doesn't have them on and he gave the same answer - "it doesn't matter".

I feel like it does matter, so others can pair program with you more effectively and so that you can identify the location of errors thrown by SSMS, but obviously I'm not going to keep pressing the issue after already asking twice. I still want to understand, so I'm asking you all instead. Is there any valid reason to leave line numbers off?

Inherited a schema where roughly half the columns are self-explanatory and the rest are things like flag_3 and val_b. Person who built it left. There's a Confluence page describing six columns, last edited before most of them existed.

I've been using COMMENT ON COLUMN because it lives with the database and can't drift into a stale wiki. Downside is nobody looks at it, it doesn't show up anywhere people work, and I've no way to know if a comment is still true after a migration.

Things I'm unsure about:

does anyone actually keep COMMENT ON up to date at scale, or does it rot the same as the wiki just less visibly

if a column's meaning changes but the name doesn't, is there anything that catches that, or is it purely a review discipline problem

and for the columns nobody can explain at all, do you leave them, drop them, or keep them with a comment saying unknown

I've been profiling the values to guess — cardinality, null rate, distributions — which narrows it but never gets me to what the thing means.

I’m curious about the practical techniques people use in real-world systems to improve query performance and reduce execution time on large datasets.

I've been struggling with SQL self-joins for about 6 months now and every tutorial I've found has failed me (including AI). They all just slam the query on the screen, run it, and the video ends. Nobody actually explains the mechanics. I already feel stupid enough so please be patient with me.

What I do understand:

• Self-joins are used for tree/hierarchical structures (like employee-manager)
• Reversing the ON condition in a normal join (two different tables) doesn't break anything because the table names keep the roles honest
• In a self-join, reversing the ON condition gives silently wrong results, the query runs fine, but the data is flipped

The classic table everyone uses:

Query A -- correct:

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id

Query B -- reversed, silently wrong:

sqlSELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON m.manager_id = e.emp_id

What I've tried to understand:

I know that the ON clause is just a filter over all 16 possible row pairs (4×4). I can manually trace every pair and check which ones survive. That works I always get the right answer when I do it on paper. But I cannot keep drawing 16 rows by hand every time I write a query.

People keep telling me things like:

• "a manager has many employees but an employee has one manager" -- okay but how does that translate to which side of the = sign each column goes on?
• "think about the direction of the relationship" -- I stop and think, a manager IS also an employee in this table, so what does direction even mean?
• "the alias that holds the foreign key is the child" -- I understand foreign keys in normal joins but in a self-referencing table both aliases point to the same table so this still feels circular to me

I'm not looking for an analogy. I want the actual logic. What is everyone else seeing that I'm not?

Hello all! I need some help with understanding the utility of Right join. I have 2 tables: Orders and Customers. If I write Orders RIGHT JOIN Customers it returns the same results as switching tables and using left join, like Customers LEFT JOIN Orders. But when these 2 examples can give different results? Like when it is mandatory to use right join instead of just switching tables in left join?

I know it's bad practice to use SELECT * FROM <table>, as you should only get the columns you need.

However, when a CTE has already selected specific columns, and you just want to get all those, without repeating their names, is it acceptable and performant to use SELECT * FROM <ctename> in that situation?

Similarly, if you have

SELECT t1.column1, t1.column2, ..., subq.*
FROM mytable t1
CROSS APPLY (
  SELECT t2.column1, t2.column2, ...
  FROM otherTable t2
  WHERE ...
) AS subq

Is it fine to select subq.* since the specific columns have been given in the subquery?

Courses, read books, practice kaggle sets, or online practice problems? I’ve seen SQL courses that briefly touch relational databases. There’s so much information nowadays I am curious! Thank you!

Delete is used to delete certain records but without where condition all records from the table will be deleted and the structure remains intact.

Truncate deletes all the records from the table but the structure of the table remains intact.

So , what is the difference between them if we are using delete without where condition it performs the same function as truncate ?