MySQL JOIN Types for LinuxFest Northwest

Today I had the opportunity to present at LinuxFest Northwest on MySQL Join Types, using the MySQL JOIN Types poster in the presentation.

It was a good session, about 30 people in attendance. Lots of great questions were asked.

Here is the download material.

MySql JOIN Types.zip

 

 

Another Corruption Challenge This Weekend.

As the weekend approaches and we head for another Database Corruption Challenge (DBCC) I want to remind everyone who is interested of being notified of the next event to sign up for my newsletter.

Just click here to find out more about the newsletter.

newsletter

Sometime Friday I will announce the latest Corruption Challenge, those on the mailing list will be the first to know when it is available.

Related Links:

Introducing Stedman Solutions, LLC.

As of today, March 31st 2015, I am off on a new adventure in my life. As of today I am 100% focused on my new business. Here are the details.

StedmanSolutionsHeader2

FOR IMMEDIATE RELEASE:

Stedman Solutions, LLC. To Offer SQL Server Consulting Services.

Bellingham, WA, USA – March 31, 2015 – Today Steve Stedman, Founder/Owner of Stedman Solutions, LLC is proud to announce a new consulting service focused on SQL Server Services, Products, and Training and more. Stedman solutions is based in the Bellingham Washington area and offers onsite services to Northwest Washington, and remote services to the world.

Steve Stedman is the founder and owner of Stedman Solutions, LLC; he has been using SQL since 1990, having 25 years of SQL and database experience. This experience combined with experience on the latest and greatest SQL Server technologies comes together to offer clients services, products and training with a specific focus on SQL Server technologies.

Services: Offering consulting, mentoring and custom solutions for all your SQL Server needs. These consulting services include performance tuning, server upgrades, data migrations, high availability, disaster recovery, business intelligence, reports and more.

  • Performance Tuning: Do you have a SQL Server that is running slow? Are you considering adding more hardware to improve the speed? Performance tuning can tune your existing server, to run faster without always requiring more hardware. Stedman Solutions can help you get the most out of your SQL Server.
  • Server Upgrades: Need help moving from that old unsupported version of SQL Server, to a newer version? Do you need help just confirming if your upgrade plan will work? Would you like to have someone available if something goes wrong? Would you prefer to just hand off the upgrade to someone else? Would you like training to prepare you for this upgrade, and many upgrades in the future? Stedman Solutions can help in all of these environments.
  • Data Migrations: Do you have data in one database that needs to be replicated or moved to another database? Do you want to move your MySQL Data into a SQL Server? For these and many more data migration solutions, Stedman Solutions is ready to help.
  • High Availability: Does your SQL Servers need to run 24×7 with minimal down time? Will your entire workforce be blocked if the SQL Server needs to be restarted? Stedman Solutions can help you improve your uptime, system reliability, and availability.
  • Disaster Recovery: What would happen if your SQL Server was destroyed by natural or human caused disaster? Do you know what risks you may be currently exposed to? Stedman Solutions can help you analyze your risks for disaster, and build a recovery plan, and help train your team to support that plan.
  • Reporting and Business Intelligence: Do you need a SSRS Report created? Do you need help with a BI dashboard? How about building a data warehouse? Stedman Solutions has experience in these areas and can with your Business Intelligence needs.

Products: Database Health Monitor – An application for DBAs and database developers currently in a free Beta release to help analyze your SQL Server to find performance bottlenecks and to find solutions for those performance issues.  Visit http://DatabaseHealth.com for more details.

Training: In addition to specific onsite and web broadcast trainings, Stedman Solutions offers training as part of every consulting engagement. Rather than just providing solutions, every client will learn so that they can do more on their own in the future.

Stedman Solutions, LLC is currently taking on new clients in all of the above mentioned areas. For free 30 minute consultation to see if we can help you, please visit http://StedmanSolutions.com or contact Steve Stedman.

 

Contact:

Steve Stedman

http://StedmanSolutions.com

PO Box 3175, Ferndale, WA 98248

 

###

FULL OUTER JOIN vs CROSS JOIN

As I have been working on the SQL Server JOIN Types poster, I have received several questions around the difference between a CROSS JOIN, and a FULL OUTER JOIN. After looking at the Venn diagrams for the two, they are both shown as the same, however they are not the same by any means.

Yes, they both include all rows from both the LEFT and RIGHT side of the JOIN, however they are matched up or JOINed in a very different way.

Lets take a look, first at the Venn diagrams, you can see below that the Venn diagrams show that all rows from Table1 are included in the results, and all rows from Table2 are included in the results. This is indeed correct, the differences come in the number of rows and how the results are matched up.

CrossJoinVennFullOuterJoinVenn

 

Now lets take a look at some sample cod that shows the differences. First we will create a database and create a couple tables to use for the demo, and each table has 3 rows inserted to it:

CREATE DATABASE [QueryTraining];
GO
USE [QueryTraining];
GO

CREATE TABLE People
(
	id   INTEGER IDENTITY NOT NULL,
	Name VARCHAR(100) NOT NULL,
    fk   INTEGER NULL
PRIMARY KEY (id)
) ;

CREATE TABLE Colors
(
	id   INTEGER NOT NULL,
	FavoriteColor VARCHAR(100) NOT NULL
) ;

INSERT INTO Colors (id, FavoriteColor) VALUES
    (1, 'red'),
    (2, 'green'),
    (3, 'blue');

INSERT INTO People (Name, fk) VALUES
    ('Steve', 1),
    ('Aaron', 3),
    ('Mary', NULL);

Look at this as a database to store people and their favorite color, in this example, there are 3 people , and 3 colors. One person does not have a favorite color, and one color has not been favorited by anyone.

Now lets take a look at the differences between the two.

CROSS JOIN

CrossJoinVenn

The CROSS JOIN gives you the Cartesian product of the two tables, by matching every row from one table with every row from another table. If there are X rows in the first table, and Y rows in the second table, the result set will have exactly X times Y rows. Notice on the CROSS JOIN, there is no ON clause specified. This is because there is not matching.

CROSS JOIN Sample

SELECT *
  FROM People p
  CROSS JOIN Colors c;

Which gives us the following result set:

CrossJoinResults

 

In the above result set, you can see that for each row in the People table (3) it was matched up with each row  from the Colors table (3) which gives us a total of 3 x 3 = 9 rows in the result set.

FULL OUTER JOIN

Now for the full outer JOIN, this is like asking for a LEFT OUTER JOIN and a RIGHT OUTER JOIN in the same query. You are asking to get every row from Table1 matching what could be matched in Table2, for those that don’t match on the Table1 side, just display the Table2 side, with NULLs on the LEFT side, and for those that don’t match on the TABLE2 side, just display the Table1 side with NULLs on the RIGHT side.

FullOuterJoinVenn
FULL OUTER JOIN Sample

SELECT *
  FROM People p
  FULL OUTER JOIN Colors c
    ON p.fk = c.id;

In this case everything from Table1 will be represented, and everything from Table2 will be shown, but they will be matched up base on the ON clause, rather than with the CROSS JOIN which gives every possible combination.FullOuterJoinResults

I hope that this helps answer some of the questions with the differences between a CROSS JOIN and a FULL OUTER JOIN.

If you haven’t already taken a look, please check out the free download of the SQL Server JOIN Types poster.

For More Information:

Brown Bag Lunch: LinkedIn Pro Tips & How to Build Your Personal Brand

This week I had a chance to sit in on a great presentation provided by Sydney Ratzlaff at Emergency Reporting.

I learned a few things, and was able to build out my LinkedIn profile based on her help.

Here are the slides from the presentation on SlideShare:

 

See Also:

Training Employees

Training Employees

A great conversation, not original, but something found on LinkedIn

For employee training, see our Free SQL Query Training program.