background preloader

SQL

Facebook Twitter

Books And Courses To Learn To Code. How to use the LEFT JOIN vs. RIGHT OUTER JOIN in SQL. You'll generally use the OUTER JOIN form that asks for all the rows from one table or result set and any matching rows from a second table or result set.

How to use the LEFT JOIN vs. RIGHT OUTER JOIN in SQL

To do this, you specify either a LEFT OUTER JOIN or a RIGHT OUTER JOIN. Using Tables We'll start simply with defining an OUTER JOIN using tables. Figure 9–3 shows the syntax diagram for creating a query with an OUTER JOIN on two tables. Figure 9–3 Defining an OUTER JOIN on two tables. Just like INNER JOIN (covered in Chapter 8), all the action happens in the FROM clause. Note For those of you following along with the complete syntax diagrams in Appendix A, SQL Standard Diagrams, note that we've pulled together the applicable parts (from Select Statement, Table Reference, and Joined Table) into simpler diagrams that explain the specific syntax we're discussing.

The critical part of any JOIN is the ON or USING clause that follows the second table and tells your database system how to perform the JOIN. The Database Programmer. Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS? - SQLPerformance.com. A pattern I see quite a bit, and wish that I didn't, is NOT IN.

Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS? - SQLPerformance.com

Let's say you want to find all the patients who have never had a flu shot. Or, in AdventureWorks2012, a similar question might be, "show me all of the customers who have never placed an order. " Expressed using NOT IN, a pattern I see all too often, that would look something like this (note that I'm using the enlarged header and detail tables from this script by Jonathan Kehayias (blog | @SQLPoolBoy)): When I see this pattern, I cringe. But not for performance reasons – after all, it creates a decent enough plan in this case: The main problem is that the results can be surprising if the target column is NULLable (SQL Server processes this as a left anti semi join, but can't reliably tell you if a NULL on the right side is equal to – or not equal to – the reference on the left side).

Instead of NOT IN, use a correlated NOT EXISTS for this query pattern. SQL Server Data Warehouse Cribsheet. For things you need to know rather than the things you want to know It is time to shed light on Data Warehousing and to explain how SSAS, SSRS and Business Intelligence fit into the puzzle.

SQL Server Data Warehouse Cribsheet

Who better to explain it all than Robert Sheldon? Contents Introduction One of the primary components in a SQL Server business intelligence (BI) solution is the data warehouse. Because of the essential role that the data warehouse plays in a BI solution, it’s important to understand the fundamental concepts related to data warehousing if you’re working with such a solution, even if you’re not directly responsible for the data warehouse itself.

Note: The purpose of this article is to provide an overview of data warehouse concepts. Just blogging my SQL Experience. SQLZOO. Learn SQL using: SQL Server, Oracle, MySQL, DB2, and PostgreSQL.

SQLZOO

Reference: how to... How to read the data from a database. 2 CREATE and DROP How to create tables, indexes, views and other things. How to get rid of them. 3 INSERT and DELETE How to put records into a table, change them and how to take them out again. 4 DATE and TIME How to work with dates; adding, subtracting and formatting. 5 Functions How to use string functions, logical functions and mathematical functions. 6 Users. Sql solace. Learn Microsoft SQL Server (2012) - Free Tutorials : SQL Server. SQL Server is a relational database management system that is capable of handling large amount of data apart from providing several methods to ensure data integrity, it also provides advance administrative utilities.

Learn Microsoft SQL Server (2012) - Free Tutorials : SQL Server

It renders complete data integrity protection and advanced security to objects. This tutorial explains the special features of the transact-SQL, statements, which provide flexible method of retrieving and modifying data in tables. The query language which enables to access data in SQL server is powerful. The purpose of this online reference is to enable the reader to comprehend the important concept of SQL Server. A Visual Explanation of SQL Joins. I love the concept, though, so let's see if we can make it work.

A Visual Explanation of SQL Joins

Assume we have the following two tables. Table A is on the left, and Table B is on the right. We'll populate them with four records each. id name id name -- ---- -- ---- 1 Pirate 1 Rutabaga 2 Monkey 2 Pirate 3 Ninja 3 Darth Vader 4 Spaghetti 4 Ninja Let's join these tables by the name field in a few different ways and see if we can get a conceptual match to those nifty Venn diagrams. There's also a cartesian product or cross join, which as far as I can tell, can't be expressed as a Venn diagram: SELECT * FROM TableA CROSS JOIN TableB This joins "everything to everything", resulting in 4 x 4 = 16 rows, far more than we had in the original sets.

MySQL

EXPLAIN EXTENDED: efficient database queries in SQL. MS-SQL, Database Development. SQL Fiddle.