“Oops! My bad.” How many times did you say this after an SQL UPDATE had gone wrong? The thing is, if you aren’t careful, a table update can have serious consequences in the form of the DELETE statement. It could become even worse if you complicate it by using UPDATE with JOIN. That’s why you need to think it over before hitting Execute or pressing CTRL-E.
So, today you will learn how to code your SQL UPDATE with JOIN without hassles and never say “Oops! My bad” again.
Read MoreCROSS JOIN is in the spotlight. This article finishes our small series of SQL JOIN-related publications. If you missed the previous two articles, refer to them as follows:
SQL Server CROSS JOIN is the simplest of all joins. It implements a combination of 2 tables without a join condition. If you have 5 rows in one table and 3 rows in another, you get 15 combinations. Another definition is a Cartesian Product.
Now, why would you want to combine tables without a join condition? Hang on a bit because we are getting there. First, let’s refer to the syntax.
Read MoreOuter 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 MoreT-SQL allows us to combine records from more than one table and return them as a single result set. This is achieved through the concept of joins in SQL Server.
This opportunity is often necessary because data in relational databases are typically normalized. For example, we have employee data spread across two or more tables. The first table would be the basic customer data and called employee. The second table would be the department.
The data consistency requires the correct relationship between the customer and the department. Returning the complete data for a set of employees and their departments requires to join both tables.
Read More