Outer join is at the center stage today. And this is part 2 of your ultimate guide to SQL joins. If you missed part 1, here’s the link.
By the looks of it, outer is the opposite of inner. However, if you consider the outer join this way, you’ll be confused. To top that, you don’t have to include the word outer in your syntax explicitly. It’s optional!
But before we dive in, let’s discuss nulls concerning outer joins.
Read MoreInner join, outer join, cross join? What gives?
It’s a valid question. I once saw a Visual Basic code with T-SQL codes embedded in it. The VB code retrieves table records with multiple SELECT statements, one SELECT * per table. Then, it combines multiple result sets into a record set. Absurd?
To the young developers who did it, it was not. But when they asked me to evaluate why the system was slow, that issue was the first to catch my attention. That’s right. They never heard of SQL joins. In fairness to them, they were honest and open to suggestions.
Read MoreHow cool is SQL COALESCE?
It’s cool enough to be so important to me. And I’ll be more than happy to hire a new guy who doesn’t have a bad habit of ignoring the goal of COALESCE. That includes other expressions and functions for handling similar situations.
Today, you will find the answers to the five most-asked questions about SQL COALESCE expression. One of these is being debated over and over again.
Shall we begin?
Read MoreHave you ever come across a situation where you needed to make users run SSIS packages? This happens when the user is not comfortable with a fixed schedule for whatever reason. You can’t define if it’s every weekend, at 9 pm, or the 5th of the month. It happens when it happens.
This sounds problematic, depending on the volume of data. If you know that a million or so records won’t be advisable to process any time of the day, say no. But if the data volume is not a problem, what are your options?
Read More“But it ran fine on our development server!”
How many times did I hear it when SQL query performance issues occurred here and there? I said it myself back in the day. I presumed that a query running in less than a second would run fine in production servers. But I was wrong.
Read MoreSQL Server Geography data type is unlike other types simply because you don’t use it often. It’s not like varchar, int, float, or DateTime present in every table, at least in one type.
Do we need the SQL Server Geography data type? To make sense of something new, we need to answer three fundamental questions:
• What problems does it solve? The answer lies in use cases.
• What are the things involved in studying it? You will learn a few basic definitions and acronyms. I collected only the important stuff, so it won’t bore you.
• Are there examples to further explain how it works and is it easy to implement? Our kind lives with code. We eat and sleep with it. So, we’re going to have sample codes in this post.
Would SQL Server graph database features fit your next project? While you may not know the definitive answer right now, you might be wondering, “What problems does it solve?”.
Read MoreDo you still hold on to the parent/child design, or would like to try something new, like SQL Server hierarchyID? Well, it is really new because hierarchyID has been a part of SQL Server since 2008. Of course, the novelty itself is not a persuasive argument. But note that Microsoft added this feature to represent one-to-many relationships with multiple levels in a better way.
You may wonder what difference it makes and which benefits you get from using hierarchyID instead of the usual parent/child relationships. If you never explored this option, it might be surprising for you.
Read MoreDo you use SQL subqueries or avoid using them?
Let’s say the chief credit and collections officer asks you to list down the names of people, their unpaid balances per month, and the current running balance and wants you to import this data array into Excel. The purpose is to analyze the data and come up with an offer making payments lighter to mitigate the effects of the COVID19 pandemic.
Do you opt to use a query and a nested subquery or a join? What decision will you make?
Read MoreFirst of all, you can’t do without them, right?
SQL data conversions or, more specifically, data type conversions are an essential part of a database developer’s or DBA’s regular programming work.
Read More