Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 20

Displaying posts with tag: join (reset)

3 Simple Patterns for Tighter MySQL Code
+0 Vote Up -0Vote Down

Join 8000 others and follow Sean Hull on twitter @hullsean. SQL is derided by many and for good reason. It’s key to scalability yet terribly difficult to write good code. Here’s a few quick tips to write tighter queries in MySQL 1. Get rid of those Subqueries! Subqueries are a standard part of SQL, unfortunately […]

The post 3 Simple Patterns for Tighter MySQL Code appeared first on Scalable Startups.

Join Optimizations in MySQL 5.6 and MariaDB 5.5
+1 Vote Up -0Vote Down
This is the third blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is targeted at the join related optimizations introduced in the optimizer. These optimizations are available in both MySQL 5.6 and MariaDB 5.5, and MariaDB 5.5
Improvements for many-table joins in MySQL 5.6
Employee +4 Vote Up -0Vote Down
A lot has happened in MySQL 5.6 for queries joining many tables. For the most common use cases we have drastically reduced the cost of finding the execution plan. We have also improved the heuristics and removed bugs so that the final plan is often better than it used to be. Read on if you are one of those people who do 15 way joins!

Finding a query execution plan
First some background. You can skip this part if you know how MySQL picks the table join order in 5.5.

When presented with a query, MySQL will try to find the best order to join tables by employing a greedy search algorithm. The outcome is what we call a query execution plan, QEP. When you join just a few tables, there's no problem calculating the cost of all join order combinations and then pick the best plan. However, since there are (#tables)! possible combinations, the cost of




  [Read more...]
MySQL DELETE Join example
+0 Vote Up -0Vote Down

A very useful helper in your join toolbox can be a delete join. Even though it’s not a special join type but a join used within DELETE statements, it’s still worth mentioning. However, from time to time when I want to make use of delete joins  on my own, I somehow managed it to forgot the syntax and have to look it up somewhere. Therefor, here is a description as well as an example.

Take care: A delete join is a very powerful weapon. And with great power comes great responsibility! I recommend to develop the delete join on a development database. At least, make sure you have a working an recent backup before trying to delete things. A delete statement that uses joins makes it easy to shot yourself in the foot. And if you do, it probably blows away your hole leg.

 

DELETE  [Read more...]

SQL JOINing a Table to Itself
+1 Vote Up -0Vote Down

Getting two sets of information from one table in a select statement often leads people to write subselects, but it really doesn't matter that this is the same table twice, we can just give it a new alias and treat it as if it were a different table. This is one of those techniques where, once you've seen it, it's really obvious, but until that point it can be very confusing. I explained this to someone else recently, so I thought I'd capture it here in case it's helpful to anyone else.

Consider that tried-and-tested example: employees and managers. Here's the staff table from the database (today's imaginary data isn't particularly imaginative, sorry):

mysql> select * from staff;
+----+------------+-----------+------------+
| id | first_name | last_name | manager_id |
  [Read more...]
70x Faster Joins with AQL now GA with MySQL Cluster 7.2
Employee +11 Vote Up -0Vote Down

70x faster joins with AQL

The new GA MySQL Cluster 7.2 Release (7.2.4) just announced by Oracle includes 2 new features which when combined can improve the performance of joins by a factor of 70x (or even higher). The first enhancement is that MySQL Cluster now provides the MySQL Server with better information on the available indexes which allows the MySQL optimizer to automatically produce better query execution plans. Previously it was up to the user to manually provide hints to the optimizer. The second new feature is Adaptive Query Localization which allows the work of

  [Read more...]
SQL Joins with On or Using
+1 Vote Up -0Vote Down

I recently wrote a post about inner and outer joins, and a couple of people asked what the difference is between USING and ON.

In a nutshell, you use ON for most things, but USING is a handy shorthand for the situation where the column names are the same.

Consider this example dataset:

mysql> select * from pets;
+---------+---------+--------+-----------+
| pets_id | animal  | name   | owners_id |
+---------+---------+--------+-----------+
|       1 | fox     | Rusty  |         2 |
|       2 | cat     | Fluffy |         2 |
|       3 | cat     | Smudge |         3 |
|       4 | cat     | Toffee |         3 |
|       5 | dog     |
  [Read more...]
Inner vs Outer Joins on a Many-To-Many Relationship
+1 Vote Up -0Vote Down

Someone will probably tell me that this is an elementary-level topic, but I got some good questions regarding joins from my most recent ZCE class students, so I thought I'd put down the examples that I used to explain this to them. Being able to join with confidence is a key skill, because it means that you can refactor and normalise your data, without worrying about how hard something will be to retrieve.

The database structure I'm using here looks like this (the same example that I used when I wrote the Databases chapter for PHP Master):



  [Read more...]
Joins in MySQL 5: #1054 – Unknown column ‘…’ in ‘on clause’
+0 Vote Up -0Vote Down

If you used to write MySQL joins for MySQL versions < 5.0 or upgrade your server from MySQL 4 to MySQL >= 5.0 you maybe run into a problem when you execute the following query:

SELECT *
FROM mytable1, mytable2
INNER JOIN mytable3
ON mytable1.mycolumnname = mytable3.mycolumnname
WHERE mytable1.id = mytable2.id;

#1054 – Unknown column ‘mytable.mycolumnname’ in ‘on clause’

Even though you made sure that the column exists, the problem persists. It can be a very annoying and time-consuming task to track this kind of error down to it’s cause: MySQL starting from version 5.0 tries to be more compliant to ANSI SQL. The tables are beeing joined in a different order. The solution to this problem is actually very simple. Surround the tables in

  [Read more...]
MySQL INNER JOIN Tutorial: the comma operator
+0 Vote Up -0Vote Down

Here you find information about writing inner joins with the comma operator. It’s the most basic way to combine (join) two tables. There is an alternative syntax that can be used, because in MySQL you can write inner joins in two different ways. Another popular way is it to use the INNER JOIN command or synonymous keywords like CROSS JOIN and JOIN. Please make sure to read our dedicated documentation for more information when you understand the comma operator syntax.

Syntax

The following examples are equivalent to the INNER JOIN examples, to make it easy to

  [Read more...]
MySQL NATURAL JOIN Tutorial & Examples
+0 Vote Up -0Vote Down

This tutorial explains how you can use NATURAL JOINs and what a natural join is actually. Included are syntax details and example statements.

Generally speaking, the keyword NATURAL can be treated as a join condition which is added implicitly. If used, it replaces the keywords ON and USING altogether. In MySQL writing natural joins means adding the keyword NATURAL to either an INNER JOIN or an OUTER JOIN. Let’s take a look at how a natural join implies a join condition.

Syntax

First of all, some natural join syntax examples. As mentioned earlier, a natural join adds a implicit condition to inner and outer join statements:

-- natural inner join
SELECT

  [Read more...]
MySQL RIGHT OUTER JOIN Tutorial & Examples
+0 Vote Up -0Vote Down

Here you find information about writing RIGHT JOINs (also referred to as RIGHT OUTER JOINs). This introduction into right joins includes a detailed description, syntax information and right outer join example statements. The Venn diagram on the left represents a result set that a statement with a right join produces. Please refer to the syntax examples below for an example. Links to additional information resources can be found at the end of this article.


Right Join syntax

First of all, some syntax examples for the impatient:

-- right join with USING-clause
SELECT *
FROM <leftTable> RIGHT JOIN <rightTable>
USING(id)


  [Read more...]
MySQL LEFT OUTER JOIN Tutorial & Examples
+0 Vote Up -0Vote Down

Here you find information about writing LEFT JOINs (also referred to as LEFT OUTER JOINs). This introduction into left joins includes a description, syntax information and example statements that use left outer joins. The Venn diagram on the left represents a result set that a statement with a left join produces. Please refer to the syntax examples below for an example. Links to additional information resources can be found at the end of this article.

Left Join syntax

First of all, some syntax examples for the impatient:

-- left join with USING-clause
SELECT *
FROM <leftTable> LEFT JOIN <rightTable>
USING(id)
-- left join with ON-clause
SELECT *
FROM


  [Read more...]
MySQL OUTER JOIN Tutorial & Examples
+0 Vote Up -0Vote Down

Here you find information about OUTER JOINs in MySQL statements. This tutorial is a general introduction. Syntax details and example statements are split into a LEFT OUTER JOIN tutorial and RIGHT OUTER JOIN tutorial because these are specific outer joins. 

Outer Join basics

MySQL (http://www.mysql.com/) supports outer joins. They identify matching rows that are stored in two different tables and add the selected columns to the result set. That’s exactly what an inner join does as well.

The difference between inner and outer join is: An outer join can identify rows without a match in the joined table. When no match was found, MySQL sets the value


  [Read more...]
MySQL INNER JOIN Tutorial & Examples
+0 Vote Up -0Vote Down

This tutorial shows you how to write ANSI-Style inner joins with the INNER JOIN keywords. Included are a general description, some syntax examples and a comparison between inner and cross joins.

Note: In MySQL the join keywords JOIN and CROSS JOIN are synonymous with INNER JOIN. That means: All example statements found in this article work fine when you replace INNER JOIN with JOIN or CROSS JOIN.


Syntax

Here are syntax examples for the impatient. Basically, ANSI-style join conditions can be specified with two different keywords: USING and ON. Take a look at the following examples:

-- inner join with USING clause
SELECT *
FROM <firstTable> a INNER JOIN <anotherTable> b
USING(<columnName>)


  [Read more...]
Basic Joins and Subqueries Video
+1 Vote Up -0Vote Down

Last month at the Boston MySQL User Group, I went through the meanings of INNER, LEFT/RIGHT OUTER, CROSS, NATURAL joins, how to do a FULL OUTER JOIN in MySQL, and what STRAIGHT_JOIN means. I also explained how to recognize when you want those types of joins, and best practices for the semantics of writing joins and design patterns. Subqueries were explained in this session, and some examples of how to think differently so that you end up writing JOINs instead of subqueries. The slides (slightly different from the slides in the video — due to error correction) can be found at http://technocation.org/files/doc/2010_01MySQLJoins.pdf.

Here’s the video:

MySQL: Another Ranking trick
+6 Vote Up -0Vote Down
I just read SQL: Ranking without self join, in which Shlomi Noach shares a nice MySQL-specific trick based on user-defined variables to compute rankings.

Shlomi's trick reminds me somewhat of the trick I came across little over a year ago to caclulate percentiles. At that time, several people pointed out to me too that using user-defined variables in this way can be unreliable.

The problem with user-defined variables

So what is the problem exaclty? Well, whenever a query assigns to a variable, and that same variable is

  [Read more...]
JOIN and comma precedence
Employee +0 Vote Up -0Vote Down

Here’s a little something that might trip you up occasionally. Have a look at this test scenario:

USE test;
DROP TABLE IF EXISTS a;
DROP TABLE IF EXISTS b;
DROP TABLE IF EXISTS c;
 
CREATE TABLE a ( a INT );
CREATE TABLE b ( b INT );
CREATE TABLE c ( c INT );
 
SELECT a.a FROM a LEFT JOIN c ON c.c = a.a; -- Q1
SELECT a.a FROM a, b LEFT JOIN c ON c.c = a.a; -- Q2

Q1 and Q2 will produce the same result, right? Wrong! As of MySQL 5.0.12, per the SQL standard, JOIN has had higher precedence than comma ‘,’.

So, you get the following:

mysql> SELECT a.a FROM a LEFT JOIN c ON c.c = a.a;
Empty set (0.00 sec)
 
mysql> SELECT a.a FROM a, b LEFT JOIN c ON c.c = a.a;
ERROR 1054 (42S22): Unknown column 'a.a' in 'on clause'

This is because, in earlier versions, MySQL interpreted it as ( ( a, b

  [Read more...]
A join I/O manipulator for IOStream
Employee +0 Vote Up -0Vote Down
I started playing around with protobuf when doing some stuff in Drizzle (more about that later), and since the examples where using IOStream, the table reader and writer that Brian wrote is using IOStreams. Now, IOStreams is pretty powerful, but it can be a pain to use, so of course I start tossing together some utilities to make it easier to work with.

Being a serious Perl addict since 20 years, I of course start missing a lot of nice functions for manipulating strings, and the most immediate one is join, so I wrote a C++ IOStream manipulator to join the elements of an arbitrary sequence and output them to an std::ostream.

In this case, since the I/O Manipulator takes arguments, it has to be written as a

  [Read more...]
Good SQL Querying
+0 Vote Up -0Vote Down

By “Good SQL Querying”, I am not referring to “how to make your queries more perfomant.” I am about to go on a mini-rant about how to make readable and self-documenting SQL queries.

One practice that will get me instantly going on a rant is using a comma join. There is NO reason to do the following:

-- uses the sakila sample database
SELECT first_name, last_name, address
FROM customer,address;

What kind of join did the original author intend? A CROSS JOIN? Or did they really want an INNER JOIN and forget the WHERE clause?

The answer: you do not know for sure; you can only guess. Had the query been

SELECT first_name,last_name,address
FROM customer INNER JOIN address;

you would know that the author intended an INNER JOIN; had the query been

SELECT first_name,last_name,address
FROM customer CROSS JOIN
  [Read more...]
Showing entries 1 to 20

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.