Extract product and variation data from WooCommerce database using SQL queries

Other Questions MySQL
1

I need to fetch both simple products and their variations from a WooCommerce database using direct MySQL queries. My hosting provider blocks REST API access so I have to work with raw SQL instead.

The goal is to get a complete list where I can see individual products and each variation separately with their pricing info. I want to build a form that lets me update prices externally without going through WordPress admin.

My current query works fine for simple products but I’m struggling with variable products. Right now it returns the parent variable product plus all variations, but I only want the individual variations themselves (not the parent). The parent product doesn’t give me control over individual variation pricing.

Here’s what I have so far:

SELECT 
    product_id, 
    item_name, 
    product_url, 
    item_type,
    code_meta.meta_value AS `PRODUCT_CODE`,
    inventory_meta.meta_value AS 'STOCK_QTY',
    discount_meta.meta_value AS 'SALE_AMOUNT',
    barcode_meta.meta_value AS 'BARCODE',
    standard_price_meta.meta_value AS 'BASE_PRICE'
FROM wp_posts
LEFT JOIN wp_postmeta code_meta ON wp_posts.ID = code_meta.post_id
    AND code_meta.meta_key = '_sku'
LEFT JOIN wp_postmeta inventory_meta ON wp_posts.ID = inventory_meta.post_id
    AND inventory_meta.meta_key = '_stock'
LEFT JOIN wp_postmeta discount_meta ON wp_posts.ID = discount_meta.post_id
    AND discount_meta.meta_key = '_price'
LEFT JOIN wp_postmeta barcode_meta ON wp_posts.ID = barcode_meta.post_id
    AND barcode_meta.meta_key = '_global_unique_id'
LEFT JOIN wp_postmeta standard_price_meta ON wp_posts.ID = standard_price_meta.post_id
    AND standard_price_meta.meta_key = '_regular_price'
WHERE item_type = 'product'

How can I modify this to include product variations while excluding their parent variable products?

2

Your query’s only grabbing item_type = 'product' which misses WooCommerce variations. WooCommerce stores variations as separate posts with post_type = 'product_variation'. I hit this same issue building a price tool for a client who couldn’t use the API. Change your WHERE clause to WHERE post_type IN ('product', 'product_variation'). Then exclude variable parent products by checking they don’t have a _product_attributes meta key with variation data. For variations, join with wp_postmeta to get the post_parent field so you can link them back to their parent product. Variations use the same pricing meta keys you’re already using, so your existing joins work fine once you fix the post type filter. Watch out though - variations sometimes inherit parent pricing, so you might get null values in price fields. Handle those cases in your form logic.

3

Try post_type = 'product_variation' in your where clause instead of item_type = 'product'. WooCommerce stores variations as separate posts, so you’ve got to query them directly. If you need parent product info, add LEFT JOIN wp_posts parent ON wp_posts.post_parent = parent.ID. This skips the variable parent products and grabs the actual variations with their pricing data.

4

You need to filter for post_type = 'product_variation' instead of item_type = 'product' to get the variations. I ran into this exact same issue when building a bulk price updater for a client. WooCommerce treats variations as child posts linked to their parent through post_parent. Your existing meta joins will work fine since variations store pricing data the same way as simple products. Couple things to watch out for: Some variations might not have _regular_price set if they inherit from the parent, so handle those cases in your form. Add AND post_status = 'publish' to avoid draft variations. If you need to tell simple products apart from variations, just add post_type to your SELECT clause. This’ll give you clean individual variation records with their specific pricing that you can update independently.