background preloader

Visual Representation of SQL Joins

Visual Representation of SQL Joins
Introduction This is just a simple article visually explaining SQL JOINs. Background I'm a pretty visual person. Things seem to make more sense as a picture. Using the code I am going to discuss seven different ways you can return data from two relational tables. For the sake of this article, I'll refer to 5, 6, and 7 as LEFT EXCLUDING JOIN, RIGHT EXCLUDING JOIN, and OUTER EXCLUDING JOIN, respectively. Inner JOIN This is the simplest, most understood Join and is the most common. Hide Copy Code SELECT <select_list> FROM Table_A A INNER JOIN Table_B B ON A.Key = B.Key Left JOIN This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). SELECT <select_list>FROM Table_A A LEFT JOIN Table_B B ON A.Key = B.Key Right JOIN This query will return all of the records in the right table (table B) regardless if any of those records have a match in the left table (table A). Outer JOIN Left Excluding JOIN Examples History - all your docs are belong to us - PHP, Perl, CSS, HTML, Java, JavaScript, MySQL, Ruby, Python, and more Cassandra vs MongoDB vs CouchDB vs Redis vs Riak vs HBase comparison :: KKovacs (Yes it's a long title, since people kept asking me to write about this and that too :) I do when it has a point.) While SQL databases are insanely useful tools, their monopoly in the last decades is coming to an end. And it's just time: I can't even count the things that were forced into relational databases, but never really fitted them. (That being said, relational databases will always be the best for the stuff that has relations.) But, the differences between NoSQL databases are much bigger than ever was between one SQL database and another. In this light, here is a comparison of Open Source NOSQL databases Cassandra, Mongodb, CouchDB, Redis, Riak, RethinkDB, Couchbase (ex-Membase), Hypertable, ElasticSearch, Accumulo, VoltDB, Kyoto Tycoon, Scalaris, OrientDB, Aerospike, Neo4j and HBase: The most popular ones Redis (V3.2) Best used: For rapidly changing data with a foreseeable database size (should fit mostly in memory). For example: To store real-time stock prices. Cassandra (2.0)

Converting a delimited string of values into columns I have seen a few questions asking how to transform a delimited values into columns, so I thought I would talk about it here. In most cases it is recommended to use a string parser function to split the string; however, today I want to talk about another method. This method takes advantage of the XML data type that was introduced in SQL Server 2005. What is nice about the XML data type is it preserves the document order. The document order is critical because it guarantees the string is kept in the same order when it is converted to XML. DECLARE @t TABLE( ProductId INT, ProductName VARCHAR(25), SupplierId INT, Descr VARCHAR(50) INSERT INTO @t VALUES (1,'Product1',1,'A1,10in,30in,2lbs'); INSERT INTO @t VALUES (2,'Product2',2,'T6,15in,30in,'); INSERT INTO @t VALUES (3,'Product3',1,'A2,1in,,0.5lbs'); Okay now we have our sample data, let’s talk about our data. Now that we have laid all the ground work, it is time to start building our query. [ProductId], [ProductName], [SupplierId], FROM @t FROM cte

How RDF Databases Differ from Other NoSQL Solutions - The Datagraph Blog This started out as an answer at Semantic Overflow on how RDF database systems differ from other currently available NoSQL solutions. I've here expanded the answer somewhat and added some general-audience context. RDF database systems are the only standardized NoSQL solutions available at the moment, being built on a simple, uniform data model and a powerful, declarative query language. In case you're not familiar with the term, NoSQL ("Not only SQL") is a loosely-defined umbrella moniker for describing the new generation of non-relational database systems that have sprung up in the last several years. Key-value databases are familiar to anyone who has worked with the likes of the venerable Berkeley DB. RDF database systems form the largest subset of this last NoSQL category. A simple and uniform standard data model. From the preceding points it follows that RDF-based NoSQL solutions enjoy some very concrete advantages such as: Data portability.

Star schema The star schema gets its name from the physical model's[2] resemblance to a star with a fact table at its center and the dimension tables surrounding it representing the star's points. Model[edit] The star schema separates business process data into facts, which hold the measurable, quantitative data about a business, and dimensions which are descriptive attributes related to fact data. Examples of fact data include sales price, sale quantity, and time, distance, speed, and weight measurements. Related dimension attribute examples include product models, product colors, product sizes, geographic locations, and salesperson names. A star schema that has many dimensions is sometimes called a centipede schema.[3] Having dimensions of only a few attributes, while simpler to maintain, results in queries with many table joins and makes the star schema less easy to use. Fact tables[edit] Fact tables record measurements or metrics for a specific event. Dimension tables[edit] Benefits[edit]

Optimizing Data Warehouse Query Performance Through Bitmap Filtering Most data warehouse queries are designed to follow a star schema and can process hundreds of millions of rows in a single query. By default, the query optimizer detects queries against star schemas and creates efficient query plans for them. One method the optimizer can use to generate an efficient plan is to use bitmap filtering. A bitmap filter uses a compact representation of a set of values from a table in one part of the operator tree to filter rows from a second table in another part of the tree. In SQL Server 2008, bitmap filtering can be introduced in the query plan after optimization, as in SQL Server 2005, or introduced dynamically by the query optimizer during query plan generation. Optimized bitmap filtering is available only on the Enterprise, Developer, and Evaluation editions of SQL Server. The bitmap filter compares favorably to the bitmap index. Comparing Bitmap Filtering with Optimized Bitmap Filtering Optimized bitmap filters have the following advantages: Example

SQL Server Index Basics Given the fundamental importance of indexes in databases, it always comes as a surprise how often the proper design of indexes is neglected. It often turns out that the programmer understands detail, but not the broad picture of what indexes do. Bob Sheldon comes to the rescue with a simple guide that serves either to remind or educate us all! One of the most important routes to high performance in a SQL Server database is the index. Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a book’s index helps you find information quickly within that book. Index Structures Indexes are created on columns in tables or views. You can create indexes on most columns in a table or a view. An index is made up of a set of pages (index nodes) that are organized in a B-tree structure. Figure 1: B-tree structure of a SQL Server index Clustered Indexes A clustered index stores the actual data rows at the leaf level of the index. Index Types

Versioning & Optimistic Locking in Hibernate - Intertech Blog By Jim White (Directory of Training and Instructor) A few weeks ago while teaching Hibernate, a student (thanks Dan) asked me about whether version numbers can be generated by the database. The answer is – Yes, in some cases. I thought the question and an expanded discussion of Hibernate’s versioning property would be a good topic for this week’s blog entry. The <version> property (or @Version annotation) For those of you that use Hibernate today, you are probably aware that Hibernate can provide optimistic locking through a version property on your persistent objects. To specify a version for your persistent object, simply add a version (or timestamp) property in your mapping file and add a version attribute to your persistent class. For those using annotations, just mark the version property’s getter in the class with the @Version annotation. @Version public long getVersion() { return version; } Hibernate: /* update com.intertech.domain.Product */ update Product set version=?

Java Persistence/ElementCollection JPA 2.0 defines an ElementCollection mapping. It is meant to handle several non-standard relationship mappings. An ElementCollection can be used to define a one-to-many relationship to an Embeddable object, or a Basic value (such as a collection of Strings). In JPA an ElementCollection relationship is defined through the @ElementCollection annotation or the <element-collection> element. The ElementCollection values are always stored in a separate table. Embedded Collections[edit] An ElementCollection mapping can be used to define a collection of Embeddable objects. The limitations of using an ElementCollection instead of a OneToMany is that the target objects cannot be queried, persisted, merged independently of their parent object. Example of an ElementCollection relationship database[edit] EMPLOYEE (table) PHONE (table) Example of an ElementCollection relationship annotations[edit] @Entitypublic class Employee { @Id @Column(name="EMP_ID") private long id; ... Basic Collections[edit]